Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Expert,
i have same excel data that is still mess, it will take a long time to fix in excel. i need help to tidy up data quickly in the Qliksense as table in below
variant | size | Type | Amount |
Crackers | Value Sales | 701000 | |
Volume (000 Units) | 1.4 | ||
12 GR | Value Sales | 701000 | |
Volume (000 Units) | 1.4 | ||
Wafer | Value Sales | 800000 | |
Volume (000 Units) | 1.4 | ||
24 GR | Value Sales | 800000 | |
Volume (000 Units) | 1.4 |
the result that i need is like this table. What kind of script to repeat the size for next row ??
variant | size | Type | Amount |
Crackers | 12 GR | Value Sales | 701000 |
Crackers | 12 GR | Volume (000 Units) | 1.4 |
Wafer | 24 GR | Value Sales | 800000 |
Wafer | 24 GR | Volume (000 Units) | 1.4 |
thanks
Try this:
IDMap: //replace this with your excel table
LOAD if(len(variant)=0,null(),variant) as variant,if(len(size)=0,null(),size)as size,Type,mount inline [
variant,size,Type,mount
Crackers,,Value Sales,701000
,,Volume (000 Units),1.4
,12 GR,Value Sales,701000
,,Volume (000 Units),1.4
Wafer,,Value Sales,800000
,,Volume (000 Units),1.4
,24 GR,Value Sales,800000
,,Volume (000 Units),1.4
];
NoConcatenate
tempID:
load if(rowno()=1,variant,if(isnull(variant),peek(variant),variant))as variant,size,Type,mount resident
IDMap;
drop table IDMap;
NoConcatenate
temp2:
load distinct variant,if(rowno()=1,size,if(isnull(size),peek(size),size))as size,Type,mount resident tempID
order by variant,size desc;
drop table tempID;
exit script;
Thanks and regards,
Arthur Fong
Thanks a lot sir