Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
chris1987
Contributor

Apply Group where contains all

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:

CategoryGenericDesign1Design2
Group1D101050M/LFA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group2D101050V/ZFA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group3D101050GC1/C2/C3/C4
Group4D101020FC1/C2/C3/C4

Item Data:

ItemAttribute string
T1234Generic=D101050,Design1=L,Design2=LL
T9876Generic=D101020,Design1=F,Design2=C2
Z1234Generic=D101050,Design1=J,Design2=C3
Z9876Generic=D101020,Design1=F,Design2=C3

And the output should be:

ItemAttribute stringGroup
T1234Generic=D101050,Design1=L,Design2=LLGroup1
T9876Generic=D101020,Design1=F,Design2=C2Group4
Z1234Generic=D101050,Design1=J,Design2=C3No Group as Design1=J and J is not included in grouping table
Z9876Generic=D101020,Design1=F,Design2=C3Group4


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

Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: Apply Group where contains all

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:

Capture.PNG

View solution in original post

5 Replies
Highlighted

Re: Apply Group where contains all

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:

Capture.PNG

View solution in original post

Highlighted
chris1987
Contributor

Re: Apply Group where contains all

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

Highlighted

Re: Apply Group where contains all

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

Highlighted
chris1987
Contributor

Re: Apply Group where contains all

Works Perfectly! Thanks!

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

CategoryGenericDesign1Design2
Group1D101050M/LFA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group2D101050V/ZFA/FD/LG/LL/M1/M2/ML/MB/HM/HP/MK/HL/HB/HK
Group3D101050C1/C2/C3/C4
Group4D101020FC1/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

Highlighted

Re: Apply Group where contains all

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