Joining rows in a group together

[jo65uk]

I have a report with the field fruit.basic.name which produces a list of fruits and how much has been sold, as below. I would like to merge alike rows


Red apples. 12.57
Green apples. 8.76
Large bananas 4.56
Small bananas 3.47
Long bananas 1.34

I would like it to look like this

Apples 21.33
Bananas 9.37

This would be on over 1000 rows

 

[hilfy]

Is there some consistency in the data? Such as the second word is ALWAYS the fruit name or the last work is always the fruit name? Without that kind of a pattern, you'll need a new table that will relate the names to the groups.

Assuming the last word is always the fruit name and that fruit names are always only one word, I would create a formula similar to this to get the group:

Right({fruit.basic.name}, Len({fruit.basic.name})-InStrRev({fruit.basic.name}) + 2, InStrRev({fruit.basic.name})

This should give you the last word in the string.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[jo65uk]

Thanks for the reply

The data is always prefixed by the word homecare-

So the data looks like

Homecare-apple
Apple
Homecare-banana
Banana

The actual data is medicines but didn't want to lists meds in a post

 

[CoSpringsGuy]

Will there ALWAYS be a dash?
If not how would the data look?
If yes, will there ever be dashes in the medicine?


_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

Try grouping on this:

stringvar array fruit := split({fruit.basicname},"-");
if ubound(fruit)>=2 then
fruit[2] else
fruit.basicname

-LB

 

[jo65uk]

Yes there will always be a dash, but there may be other maedicines with a dash in.

The unique identifier to these rows is the prefix HOMECARE-

The field name is drug_basic.name

 

[IanWaterman]

Group on formula

@Group

If Fieldname like 'HOMECARE-*' then mid(Fieldname, 10, 50) else Fieldname

Change 50 for max length of fields, if name not that long dont worry formula will only bring back what is there.

Ian

 

[lbass]

You can just use:

mid({table.field},10)

-LB

 

[jo65uk]

Group on formula

@Group

If Fieldname like 'HOMECARE-*' then mid(Fieldname, 10, 50) else Fieldname

Change 50 for max length of fields, if name not that long dont worry formula will only bring back what is there.

Ian

This worked fine

Thank you

Joanne

 

[lbass]

You don't need the "50" argument--if you leave it out, it will include everything after the first 10 characters.

-LB

 

[jo65uk]

I have used this formula

If Fieldname like 'HOMECARE-*' then mid(Fieldname, 10, 50) else Fieldname

I would like to do this for multiple prefixes

If Fieldname like 'HOMECARE-*, CIVAS-*' then mid(Fieldname, 10, 50) else Fieldname

but this doesn't work

Any ideas?

Joanne

 

[IanWaterman]

As LB says you can drop the 50, I always forget it defaults to string length

you need to enclose each string
If Fieldname like ['HOMECARE-*', 'CIVAS-*'] then mid(Fieldname, 10) else Fieldname

If that does not work you will need to do multiple if then else

If Fieldname like 'HOMECARE-*' then mid(Fieldname, 10) else
If Fieldname like 'CIVAS-*' then mid(Fieldname, 10) else
.....

else Fieldname

Ian

 

[jo65uk]

If {drug_basic.drugname}like 'HOMECARE-*, CIVAS -*, CYTO - *' then mid({drug_basic.drugname}, 10) else {drug_basic.drugname} else
If {drug_basic.drugname}like 'CYTO - *' then mid({drug_basic.drugname}, 10) else {drug_basic.drugname}

I get the message - The remaining text does not a ppear to be part of the formula - from else highlighted

Joanne

 

[jo65uk]

If {drug_basic.drugname}like 'HOMECARE-*, CIVAS -*, CYTO - *' then mid({drug_basic.drugname}, 10) else {drug_basic.drugname} else
If {drug_basic.drugname}like 'CYTO - *' then mid({drug_basic.drugname}, 10) else {drug_basic.drugname}

I get the message - The remaining text does not a ppear to be part of the formula - from else highlighted

Joanne

 

[IanWaterman]

Two problems

YOur multiple like list must be enclosed in square brackets []and you have not split each text block with ' ', eg 'HOMECARE-*'

other wise it will search for this string HOMECARE-*, CIVAS -*, CYTO - *

you have an extra else without an if

If {drug_basic.drugname}like ['HOMECARE-*', 'CIVAS -*, 'CYTO - *'] then mid({drug_basic.drugname}, 10)

else {drug_basic.drugname} // this is floating around

else
If {drug_basic.drugname}like 'CYTO - *' then mid({drug_basic.drugname}, 10) else {drug_basic.drugname}

Why are you duplicating the 'CYTO - *' test?

this is all you should need

If {drug_basic.drugname}like ['HOMECARE-*', 'CIVAS -*', 'CYTO - *'] then mid({drug_basic.drugname}, 10)
else {drug_basic.drugname}

Ian

 

[jo65uk]

Can I do anything about the length "10" as they are all different

Joanne

 

[IanWaterman]

That's a good point did not spot that you will have to use the multiple If then else

If Fieldname like 'HOMECARE-*' then mid(Fieldname, 10) else
If Fieldname like 'CIVAS-*' then mid(Fieldname, 7) else
If Fieldname like 'CYTO-*' then mid(Fieldname, 6) else
else Fieldname

Ian

 

[jo65uk]

Perfect

Thanks for you help

Joanne