Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anya
Contributor III
Contributor III

Repeat A row

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

variantsizeTypeAmount
Crackers Value Sales701000
  Volume (000 Units)1.4
 12 GRValue Sales701000
  Volume (000 Units)1.4
Wafer Value Sales800000
  Volume (000 Units)1.4
 24 GRValue Sales800000
  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 ??

variantsizeTypeAmount
Crackers12 GRValue Sales701000
Crackers12 GRVolume (000 Units)1.4
Wafer24 GRValue Sales800000
Wafer24 GRVolume (000 Units)1.4

 

thanks 

2 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

MC.PNG

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

anya
Contributor III
Contributor III
Author

Thanks a lot sir