Multiple groups by Value in String

[mattz196]

After searching through many posts I am unable to work out how to do the following.
I would like to group items by a value in a string(category) this item may belong to more than one group.


Eg

Item Category
1 A
2 B
3 AB
4 CA

Resulting groups I would like

Category Item
A 1
4

B 2
3

C 4

Category may vary in length, I am able to format data in "category" to include a deliminator if required.

Trust I have included enough info .
Thank you in advance for any help you may be able to offer

 

[mattz196]

p.s Using crystal v 8.5

 

[Madawc]

Do a formula field, @Truecategory

if Category like "A" then "A"
else if Category like "B" then "B"

and so on, maybe ending with X if nothing is found. The like means that it looks for the value within the string, so it would find A within CA etc.

Now group on @Truecategory

Madawc Williams (East Anglia)

 

[mattz196]

Tried using
if Category like "A" then "A"
else if Category like "B" then "B"

but if condtion is false
eg {category} string begins with "B" or is "AB" it does not get grouped.

Any further help would be appreciated.
Matt

 

[Madawc]

That surprises me, because I did something similar and it worked.

I suggest you first try displaying @Truecategory on detail lines, to see what you get.

If that doesn't work, do separate formula fields for each 'like' test, and then do an if ... else on those results.

Madawc Williams (East Anglia)

 

[mattz196]

Madawc
Thanks for your assistance , I tried using formula in detail section , but again as soon condtion is false
eg {category} string begins with "B" or is "AB" I get no result.

Thanks

Matt

 

[synapsevampire]

A grouping collects 1 of each item into a respective hierarchy, it does NOT duplicate data.

You show Catgory A and C containing Item 4, that means 2 rows, but there's only 1 row.

However your example doesn't show Item 3 in category A, though there is an item 3 for category AB.

Pretty confusing, and seemingly to you as well.

I would suggest reading up on grouping data, reposting an accurate representation of example data and the expected output.

If you want to fabricate data across numerous groups, I would probably approach this by normalizing the data on the database side.

You might also create an array to count itmes in numerous buckets, but you'll have a 254 char limit output.

Perhaps I'm misunderstanding something here, but the post looks whacked.

-k

 

[mattz196]

Sorry to be confusing I did make an error in my example, my apologies I will try again.

Item Category
1 A
2 B
3 AB
4 CA

Expected output

Group A
Item 1
Item 3
Item 4

Group B
Item 2
Item 3

Group C
Item 4

Once again thanks again for assistance and apologies for confusion.
Matt

 

[mbarron]

Can't be done.
You are trying, as SV points out, to put one bit of data into 2 groups.

A fruity example:
You're the local produce guy and you have to sort the stock. The night shift guy knocked over some boxes and threw everything into one big box. You're looking at golden delicious apples, macintosh apples, red tomatoes,and yellow tomatoes (yes, tomatoes are fruits, but that's a different forum)

You want to sort everything into seperate boxes. You have one for yellows, reds, apples, and tomatoes. You pull a fruit (a macintosh) out of the main box you put it into a box. The first box you come to is the "red" box, so you put it in. Then you realize you have an apple box. Now what? You can't have the same apple in two boxes at once.

You can however, have a shelf for the reds and yellows with boxes for apples and tomatoes. Or, a shelf for apples or tomatoes with boxes for reds and yellows (multi layer / hierarchy groups).



Mike

 

[mattz196]

Thanks any way cheers