Discussion Board for collaboration on QlikView Scripting.
Struggling with defining a script.
The transaction lines look like this:
The discount applies to the bananas.
I want to make a kpi on the amount of discount given, per reason code.
What I need to do is include the information of lines 100.3 and 100.4 into line 100.2.
Any suggestions. 'Guess it would be something with a peek function, but maybe you have solution for me out of the box.
You could maybe strip the discount and reason data into a separate table:
//Load all Tx data
//Extract discount data. Using space as the delimiter break up the description of rows without an Item into
//NameValue pairs (Detail and Data e.g. 'Discount' and '10%', 'Reason:' and 'Best before end date exceeded')
,SubField(Description,' ',1) AS DiscountDetail
,SubField(Description,' ',2) AS DiscountData
WHERE Len(Item) = 0;
//Delete all rows in Transaction table that aren't normal sales lines
INNER JOIN (Transaction) LOAD * RESIDENT Transaction WHERE Len(Item) > 0;
Depending on the rest of your data model you could even put the SubField() code into the main Transaction table load to save creating another table.
Hope this helps,