Apply Group where contains all

[chris1987]

I'm trying to group items based on the attributes they have. I have a master table which contains the group info and each item has a concatenated list of attributes. To add to this the master table has multiple options, separated by a "/". For example:

Grouping Table:

Category	Generic	Design1	Design2
Group1	D101050	M/L	FA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group2	D101050	V/Z	FA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group3	D101050	G	C1/C2/C3/C4
Group4	D101020	F	C1/C2/C3/C4

Item Data:

Item	Attribute string
T1234	Generic=D101050,Design1=L,Design2=LL
T9876	Generic=D101020,Design1=F,Design2=C2
Z1234	Generic=D101050,Design1=J,Design2=C3
Z9876	Generic=D101020,Design1=F,Design2=C3

And the output should be:

Item	Attribute string	Group
T1234	Generic=D101050,Design1=L,Design2=LL	Group1
T9876	Generic=D101020,Design1=F,Design2=C2	Group4
Z1234	Generic=D101050,Design1=J,Design2=C3	No Group as Design1=J and J is not included in grouping table
Z9876	Generic=D101020,Design1=F,Design2=C3	Group4

As you can see in the grouping table there are multiple options separated by a "/". In PHP / VBA what I'd do is explode these into an array, append "Design2=" to the beginning of the option (so it would become "Design2=FA " then "Design2=FD" etc. and loop through the array checking each string for a match - but I'm unsure of the best way to do this in QV.

Any help would be appreciated!

Thanks

Chris

[tamilarasu · Accepted solution]

Hi Chris,

Group:
Mapping LOAD SubField(Design1,'/') & SubField(Design2,'/') as Design
     ,Category
     FROM
[https://community.qlik.com/thread/295868]
(html, codepage is 1252, embedded labels, table is @1);

Item:
Load Item
,[Attribute string]
,ApplyMap('Group',Design, 'No Group as Design1=' & TextBetween([Attribute string],'Design1=',',') & ' and ' &  TextBetween([Attribute string],'Design1=',',')
                                          & ' is not included in grouping table') as Group;
LOAD Item,
     [Attribute string],
     TextBetween([Attribute string],'Design1=',',') & TextBetween([Attribute string],'Design2=','') as Design
FROM
[https://community.qlik.com/thread/295868]
(html, codepage is 1252, embedded labels, table is @2);

Result:

[chris1987]

Thanks that's great!

Could I ask that you take it one step further and add "Generic" in there too, so the Generic has also got to match?

Really impressed with the code!

Thanks

Chris

[tamilarasu]

Great . Here is the updated code.

Group:
Mapping
LOAD  Generic & SubField(Design1,'/') & SubField(Design2,'/') as DesignKey
     ,Category
FROM
[https://community.qlik.com/thread/295868]
(html, codepage is 1252, embedded labels, table is @1);

Item:
Load Item
,[Attribute string]
,ApplyMap('Group', DesignKey, 'No Group as Design1= TextBetween([Attribute string],'Design1=',',') & ' and ' &  TextBetween([Attribute string],'Design1=',',')
                                          & ' is not included in grouping table') as Group;
LOAD Item
     ,[Attribute string]
     ,TextBetween([Attribute string],'Generic=',',') & TextBetween([Attribute string],'Design1=',',') & TextBetween([Attribute string],'Design2=','') as DesignKey
FROM
[https://community.qlik.com/thread/295868]
(html, codepage is 1252, embedded labels, table is @2);

[chris1987]

Works Perfectly! Thanks!

One last one - which may be a step too far, sometimes "Design1" can be blank to allow all options:

Category	Generic	Design1	Design2
Group1	D101050	M/L	FA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group2	D101050	V/Z	FA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group3	D101050		C1/C2/C3/C4
Group4	D101020	F	C1/C2/C3/C4

Obviously how the DesignKey is created at the minute an exact match is required. Is there a way to somehow use a wildcard in the DesignKey?

So group 3 Design Key could be D101050*C1, D101050*C2 etc.

Cheers

Chris

[tamilarasu]

Chris,

Here you go.

Group:
Mapping
LOAD  Generic & SubField(Design1,'/') & SubField(Design2,'/') as DesignKey
      ,Category
FROM
[https://community.qlik.com/thread/295868]
(html, codepage is 1252, embedded labels, table is @4);

Item:
Load Item
     ,[Attribute string]
     ,DesignKey1
     ,DesignKey2
     ,Applymap('Group', DesignKey1, Applymap('Group', DesignKey2, 'No Group as Design1=' &
              TextBetween([Attribute string],'Design1=',',') & ' and ' & TextBetween([Attribute string],'Design1=',',') & ' is not included in grouping table')) as Group;
LOAD Item
     ,[Attribute string]
     ,TextBetween([Attribute string],'Generic=',',') & TextBetween([Attribute string],'Design1=',',') & TextBetween([Attribute string],'Design2=','') as DesignKey1
     ,TextBetween([Attribute string],'Generic=',',') & TextBetween([Attribute string],'Design2=','') as DesignKey2
FROM
[https://community.qlik.com/thread/295868]
(html, codepage is 1252, embedded labels, table is @2);