Grouping of GL accounts

[Newbie777]

Hello,

I'd like to group GL accounts with in the way where certain items can appear multiple times in the mapping like shown below:

Fruit	Group 1	Group 2	Group 3
Apple	All	All except Cranberry	All except Cranberry & Banana
Orange	All	All except Cranberry	All except Cranberry & Banana
Kiwi	All	All except Cranberry	All except Cranberry & Banana
Banana	All	All except Cranberry
Cranberry	All

I'd like to create a filter like below

Grouping

All

All except Cranberry

All except Cranberry & Banana

where particular GL accounts appear multiple times in the grouping, and thus, I cannot use normal mapping structure where each GL appear only once, in order to create a filter like above, which behaves more like subtotals. 

Is there a way to create this as a dimension field?  Please let me know how I can do this. 

Thank you very much for your help! 

[rubenmarin]

Hi an easy way is to realte each Furit to it's different gorups, base on the first table it could be something like:

FinalTable:
LOAD Fruit, Group1 as Group
Resident Table1;

Concatenate
LOAD Fruit, Group2 as Group
Resident Table1;

Concatenate
LOAD Fruit, Group3 as Group
Resident Table1;

DROp Table table1;

This way you ha ve a Group Field that filters the associated fruit values

[Newbie777]

Thanks a lot for your response. I am still new to Qlik Sense, and am not sure where I should put that command.  

Is it in 'Data load editor' I should put those lines you wrote above?  Also, should I include Group 1, Group 2, and Group 3 fields in the table when attaching excel file?  

Thank you for your help.  

[rubenmarin]

Hi, yes, it's on data laod editor. You don't need to load those 3 fields i f you want one field to selet, all three will be merged in a single 'Group field.

Is the first table what you have on excel? you can load this on a temporary table:

tmpExcelData:
LOAD Fruit,
  Group1,
  Group2,
  Group3,
From [....xls] (...);

Use this data to create the 'Group' field as said in the previous post, just change Table1 to this temporpary table name, and add a condition to load records with values:

FinalTable:
LOAD Fruit, Group1 as Group
Resident tmpExcelData
Where Len(Trim(Group1))>0;

Concatenate
LOAD Fruit, Group2 as Group
Resident tmpExcelData
Where Len(Trim(Group2))>0;

Concatenate
LOAD Fruit, Group3 as Group
Resident tmpExcelData
Where Len(Trim(Group3))>0;


DROp table tmpExcelData;

[Newbie777]

Thanks a lot for your advice! I will try to copy and study your syntax, and see how it will work with my real data.  BTW, I presume that Qlik Sense syntax are sensitive to lower and upper cases.  Is 'DROp' has to be followed in that manner in terms of upper and lower cases?  

[rubenmarin]

Something are case sensitive, mostly field values or field names, in that case doesn't matter, it can be Drop, DROP, dRoP

[Newbie777]

OK. Thanks for letting me know.