2 Replies Latest reply: Jun 2, 2009 1:45 PM by LarsMadsen RSS

    Load records repeatedly

      Hi QlikCommunity!

       

      I am often struggling with repeatedly loading single records with the condition that the number of iterations should be the variance between a field currently read and the same field value in the next record.

       

      Example:

      The enclosed QlikView application shows a typical setup from our ERP system. This table contains information about cost price calculation(IDs). In this case 3 calculations have been made for item 166404 and 2 for 165000. The "transdate" = validation date and thus will hold until a new calculation is performed with a newer TransDate.

       

      Sine I want to use this table as a link table to decide which particular PriceCaldID is valid for a certain ItemID at a certain production time (=TransDate), I figured that continuously loading each record with something like this:

       

      Load ItemID,

      PriceCalcID,

      Transdate + Iterno() as Valid_date

       

      While IterNo() <= Previous(transdate) - Transdate

       

      Order by ItemID Desc,

      PriceCalcID Desc,

      Transdate Desc

       

      Resident DATA

       

      (since I only know how to read a value from a preceeding record I obviously suggest to load the data in Descending order)

       

      Anyway - what I need is this result:

       

      ITEMID

      PRICECALCID

      TRANSDATE

      Reload add.

      166404

      1077

      13-05-2009

      166404

      1077

      14-05-2009

      Added

      166404

      1080

      15-05-2009

      166404

      1081

      16-05-2009

      165000

      1090

      29-05-2009

      165000

      1090

      30-05-2009

      Added

      165000

      1091

      31-05-2009



       

      I appreciate your feedback

       

      Regards,

      Lars Madsen