Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Newbie7
Partner
Partner

Grouping of GL accounts

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:

FruitGroup 1Group 2Group 3
AppleAllAll except CranberryAll except Cranberry & Banana
OrangeAllAll except CranberryAll except Cranberry & Banana
KiwiAllAll except CranberryAll except Cranberry & Banana
BananaAllAll except Cranberry 
CranberryAll  

 

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! 

6 Replies
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

Newbie7
Partner
Partner
Author

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?  

Newbie7_0-1624095993641.png

 

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;
Newbie7
Partner
Partner
Author

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

Newbie7
Partner
Partner
Author

OK. Thanks for letting me know.