1 Reply Latest reply: Jul 16, 2012 7:00 PM by Jason Michaelides RSS

    how to combine transaction lines

    Johan Vermeulen

      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.

        • Re: how to combine transaction lines
          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