3 Replies Latest reply: Sep 13, 2011 8:06 AM by Martin Ekelund RSS

    Remove data in script

      Dear all,

       

      I'm new to qlikview and I would like help with a problem I have encountered. In my script I’m loading data from a single textfile with orderlines that are delivered on acertain date.

       

      The textfile contains data like below:

       

      Deliveries:

       

      LOAD

      [Material number],

      [Material description],

      [Order no],

      Item,

      ([Order no]&Item) as ItemID,

      [GR date]

       

      FROM

      $(vPathQvdRaw)\Data\Deliveries.txt

      (txt, codepage is 1252, embedded labels, delimiter is '\t');

       

      The ItemID is a unique number for each orderline. But then one ItemID can be delivered on different occasions. I only want to keep the ItemID with the latest [GR date]. For this I usually sort my data in excel so that I have ItemID ascending and the latest [GR date] on the top row and then all the other data below. Then I check if the ItemID on one row is the same as the previous one and if it is I mark this line with an x. Then I just delete all the rows marked with an x.

       

      How can I do this in qlikview?

       

      Would very much appreciate your help.

       

      Thanks in advance,

       

      Martin

        • Remove data in script
          Rahul Gupta

          Hey hi,

           

          Use this code:

           

          LOAD  [Material number],
          [Material description],
          [Order no],
          Item,
          ([Order no]&Item) as ItemID,
          [GR date]
          FROM
          $(vPathQvdRaw)\Data\Deliveries.txt
          (txt, codepage is 1252, embedded labels, delimiter is '\t');


          LEFT JOIN

          LOAD  ([Order no]&Item) as ItemID,
          mAX([GR date]) AS [Max GR Date]
          FROM
          $(vPathQvdRaw)\Data\Deliveries.txt
          (txt, codepage is 1252, embedded labels, delimiter is '\t')
          Group By [Order no]&Item;


          Now, Take the [Max GR Date] field instead of [GR Date] in the chart.

           

          Moreover, if you want to keep only the Max Values to exist than Use Right Join Instead of Left Join.

           

           

          Hope this helps...

           

          Regards

          • Remove data in script

            Hi all,

             

            Thanks for the help above. When the script loads the data, the number of orderlines that are extracted is correct. What I now want to do is to only keep the rows that are defined and scrapt all the other ones.

             

            How can I do this?

             

            Thanks in advance,

             

            Best regards

            Martin