3 Replies Latest reply: Sep 21, 2016 4:11 PM by Stefan Wühl RSS

    Loading only the most recent date from the date field

    Carl Safford

      Howdy Qlikers,

       

      I do know there are other questions out there like this, but as of yet I haven't come across one that quite gets to what I need to do, so please point me to the right thread if you know of it!

       

      I have data that looks something like this

       

      Temp:

      Part Number, Extraction Date, Quantity

      PN1, 42632, 1000000

      PN1, 42620, 750000

      PN2, 42625, 2000000

      PN3, 42632, 1500000

      PN4, 42632, 250000

      PN4, 42600, 150000

      PN5, 42555, 1250000

       

      I am interested in loading only the rows that fall under the most recent extraction date, in this particular case, only those with the value 42632.  However, I'd like to make the app dynamic as new extracts get added to the database.  The "filter" that I currently have on my data load does not quite do this (code as follows):

       

      filter:

      inner keep load

      "Part Number",

      max([Extraction Date]) as [Extraction Date]

      group by "Part Number";

      drop table filter;

       

      Results on Sample table "Temp" using Above Code:

      PN1, 42632, 1000000

      PN2, 42625, 2000000

      PN3, 42632, 1500000

      PN4, 42632, 250000

      PN5, 42555, 1250000


      Desired Results:

      PN1, 42632, 1000000

      PN3, 42632, 1500000

      PN4, 42632, 250000

       

       

       

      Any help you lovely people could provide would be most appreciated.