Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
petber_exsitec
Contributor III
Contributor III

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

View solution in original post

petber_exsitec
Contributor III
Contributor III

It's wokring. Thank you Gysbert for the help