Splice

[Former Member]

Hello,

CRXI R2

I have a excel file that has data such as

jdoe#Engineer@123.12.1.1

jdoe@123.12.1.1

I would like to extract "jdoe" and count it. What functions in the formula do I use to extract jdoe from the data?

Still new to the tool...

thanks...

[former_member292966 · Accepted solution]

Hi Sterling,

You can use a formula like:

{TABLE.Field1} [1 to 3];

This will extract the first 3 characters from your column. You can then drop it onto your Detail section and right-click and go to Insert | Summary and select Count as the summary type.

Good luck,

Brian

Edited by: Brian Dong on Oct 21, 2008 6:22 PM

[Former Member · Accepted solution]

All,

Thanks for the replies, but I think I wasn't clear enough. The data before the "#" and "@" varies such as this:

jdoe#Engineer@123.12.1.1

jdoe@123.12.1.1

frank.smith@123.12.1.1

scmtools#CQMS@123.12.1.1

sferg@123.12.1.1

So....

I need to gather the data before the "#" and "@" and then (?) and (?)

to make my final report say:

jdoe from the (blah) department accessed (blah) 2 times

frank.smith from the....

etc....

SFII

[Former Member · Accepted solution]

This will give you what you want by splitting the string as needed. See if it helps you out any. You will have to modify it to suit your needs.

stringvar x := 'frank.smith@123.12.1.1';

numbervar a := instr(x,'#');

numbervar b := instr(x,'@');

if a > 0 then stringvar y := split(x,'#')[1];

if a > 0 and b > 0 then stringvar z := split(x[a+1 to length(x)],'@')[1]

else if a = 0 then z := split(x,'@')[1];

x & ' ' & y & ' ' & z;

PS: Part of the formula is being converted into an URL. It should be SPLIT (X AND THEN OPENING SQUARE BRACKET AND A+1 TO LENGTH(X) AND CLOSING SQUARE BRACKET AND ,'@')

Edited by: Sanjay Kodidine on Oct 21, 2008 8:05 PM

[Former Member]

Hello,

well firstly how are you getting your data into crystal reports? If you are using Excel as your data source, the it should display jode# as primary ID, if it is then simply click on "Summary" icon and choose the field from the drop down menu, select the summary type as count and place it in the Report footer.

That will count all of it once.

If you want to see them in detail then place it in the detail section.

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Oct 21, 2008 5:30 PM

Ignore what I said above as I replied thinking totally different question as to what has been asked!

My sincere apologies!!

Regards

jehanzeb