Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Joining rows in a group together

Status
Not open for further replies.

jo65uk

IS-IT--Management
Feb 11, 2003
11
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
 
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
 
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
 
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

 
Try grouping on this:

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

-LB
 
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
 
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
 
You can just use:

mid({table.field},10)

-LB
 
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
 
You don't need the "50" argument--if you leave it out, it will include everything after the first 10 characters.

-LB
 
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
 
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




 
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
 
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
 
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


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

Joanne
 
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
 
Perfect

Thanks for you help

Joanne
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top