Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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;
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?
Something are case sensitive, mostly field values or field names, in that case doesn't matter, it can be Drop, DROP, dRoP
OK. Thanks for letting me know.