topic Re: Best way to Group Fields in QlikView

Best way to Group Fields

rido1421 — Wed, 17 Sep 2014 10:50:49 GMT

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

Re: Best way to Group Fields

MK_QSL — Wed, 17 Sep 2014 10:53:09 GMT

Use below in script...

PurgeChar(Customers,'0123456789') as Customers

Re: Best way to Group Fields

aveeeeeee7en — Wed, 17 Sep 2014 10:56:38 GMT

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

Re: Best way to Group Fields

rido1421 — Wed, 17 Sep 2014 10:59:09 GMT

Hi Manish

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

Cars may have:

Cars SoutAfrica 12

Re: Best way to Group Fields

Wed, 17 Sep 2014 11:02:38 GMT

try this

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

Re: Best way to Group Fields

MK_QSL — Wed, 17 Sep 2014 11:03:36 GMT

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

Re: Best way to Group Fields

rido1421 — Wed, 17 Sep 2014 11:06:42 GMT

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

Re: Best way to Group Fields

rbecher — Wed, 17 Sep 2014 11:12:47 GMT

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

Re: Best way to Group Fields

rido1421 — Wed, 17 Sep 2014 11:33:55 GMT

Thanks I will try this

Re: Best way to Group Fields

Anonymous — Wed, 17 Sep 2014 11:36:53 GMT

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.

Re: Best way to Group Fields

rido1421 — Wed, 17 Sep 2014 12:34:21 GMT

Hi Avee

Please could you advise if there is an equivilent to

If(Not(Wildmatch(Customers,'*Cars*','*Taxis*'),'Cars' as Others

I would like to Label all those that have not been grouped as others...

Thanks