Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

how to combine transaction lines

Hi,

Struggling with defining a script.

The transaction lines look like this:

TransactionLineItemDescriptionPrice
1001XAppels €          6,00
1002YBananas €          3,00
1003 Discount 10%
1004 Reason: Best before date exceeded
1011ZLemons €          7,00
1012XAppels €          6,00

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.

Thanks,

Johan.

1 Reply
jason_michaelid
Honored Contributor II

Re: how to combine transaction lines

You could maybe strip the discount and reason data into a separate table:

//Load all Tx data

Transaction:

LOAD

     Transaction

     ,Line

     ,Item

     ,Description

     ,Price

FROM....;

//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')

Discounts:

LOAD

     Transaction

     ,SubField(Description,' ',1)     AS     DiscountDetail

     ,SubField(Description,' ',2)     AS     DiscountData

RESIDENT Transaction

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,

Jason

Community Browser