Best way to Group Fields

[rido1421]

Hi All

I have a table of Customers

My requirement is to group these customers

Customers

Cars1

Cars2

Cars3

Cars4

Cars5

SoccerBalls1

SoccerBalls2

SoccerBalls3

Paper1

Paper2

Paper3

Paper4

I need to group them into

Cars

SoccerBalls

Paper

I can use the if(Customers like '*Cars*', 'Cars') as GroupName

but this will take forever as my list of clients is huge.

also within the data i would need to do two like clauses for one group name, how would I do that?

eg. if(Customers like '*Cars* and Like '*Taxis*','Cars') as GroupName   ---- I have Tried this it doesnt work.

Thank you

[MK_QSL]

Use below in script...

PurgeChar(Customers,'0123456789') as Customers

[aveeeeeee7en · Accepted solution]

Hi

Use Wildmatch() Function:

If(Wildmatch(Customers,'*Cars*','*Taxis*'),'Cars',

If(Wildmatch(Customers,'*Soccer*'),'Soccer',

If(Wildmatch(Customers,'*Paper*'),'Paper',Customers))) AS GroupName

Create this in Script and after reload use this field in Front-end.

Regards

Aviral Nag

[rido1421]

Hi Manish

My actual data is not as simple as above, for example

Cars  may have:

Cars SoutAfrica 12

[#5]

hi

try this

if(wildmatch(Customers like '*Cars*', '*Taxis*'), 'Cars' ) as GroupName

[MK_QSL]

Kindly provide some more samples to create a single code for you....

[rido1421]

Thank you , I guess there is no way to avoid doing it manually

[rbecher]

Hi Rido,

this is how I would do:

1. Create a Mapping table to tag the occurances (easy to maintain)

2. Use MapSubstring/Textbetween

TagList:

MAPPING LOAD * INLINE [

    SubStr, GroupTag

    car, <Cars>

    truck, <Cars>

    taxi, <Cars>

    soccer, <Sports>

    ball, <Sports>

    paper, <Office>

    pen, <Office>

];

Data:

LOAD Customers, TextBetween(MapSubString('TagList', Lower(Customers)), '<', '>') as Group INLINE [

    Customers   

    Cars1

    Cars2

    Cars3

    Cars4

    Cars5

    SoccerBalls1

    SoccerBalls2

    SoccerBalls3

    Paper1

    Paper2

    Paper3

    Paper4

];

- Ralf

[rido1421]

Thanks I will try this

[Anonymous]

Hi Redo,

You can try below script.

if(Match(Customers,'Cars1','Cars2','Cars3','Cars4','Cars5'), 'Cars',

if(Match(Customers, 'SoccerBalls1','SoccerBalls2','SoccerBalls3'),'SoccerBalls',

if(Match(Customers,'Paper1','Paper2','Paper3','Paper4'),'Paper'))) as Customers1

Hope this would be helpful for you.