how to combine transaction lines

[#1]

Hi,

Struggling with defining a script.

The transaction lines look like this:

Transaction	Line	Item	Description	Price
100	1	X	Appels	€          6,00
100	2	Y	Bananas	€          3,00
100	3		Discount 10%
100	4		Reason: Best before date exceeded
101	1	Z	Lemons	€          7,00
101	2	X	Appels	€          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.

[Jason_Michaelides]

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