Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

Highlighted
Contributor III
Contributor III

Thanks a lot sir