Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Script to transform Exceltable with slow moving categories

Hi

I have a inputtable in excel with categories and then datarows below. On each datarow I would like to know the category that the datarow belongs to. Se the pictures as sample input. I also attach a small samplefile in excel. How can I make the resulttable with script in Qlikview. Any ideas?

input.png

result.png

Best regards

Peter

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Perhaps like this:

Data:

LOAD *

WHERE Qty <> 'Qty' ;

LOAD

  If(Previous(B)='Qty' ,Previous(A),Peek(Category)) as Category,

  A as Item,

  B as Qty,

  C as Value

FROM

[Sample_data.xlsx]

(ooxml, no labels, table is Input)

WHERE Len(Trim(B))>0;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

Perhaps like this:

Data:

LOAD *

WHERE Qty <> 'Qty' ;

LOAD

  If(Previous(B)='Qty' ,Previous(A),Peek(Category)) as Category,

  A as Item,

  B as Qty,

  C as Value

FROM

[Sample_data.xlsx]

(ooxml, no labels, table is Input)

WHERE Len(Trim(B))>0;


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

It's wokring. Thank you Gysbert for the help