topic Re: Loading only the most recent date from the date field in App Development

Loading only the most recent date from the date field

Wed, 21 Sep 2016 15:10:21 GMT

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.

Re: Loading only the most recent date from the date field

swuehl — Wed, 21 Sep 2016 18:30:13 GMT

What if you don't group by Part Number?

Temp:

LOAD * INLINE [

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

];

INNER JOIN

LOAD

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

RESIDENT Temp

;

Re: Loading only the most recent date from the date field

Wed, 21 Sep 2016 19:45:44 GMT

It worked! Overlooked the simple solution. I think I was expecting there to an error without a 'group by' statement in there for some reason. Thank you much!

Re: Loading only the most recent date from the date field

swuehl — Wed, 21 Sep 2016 20:11:51 GMT

You only need to list all fields in a group by clause that are not included in an aggregation function.

If you don't have any other fields besides the one in the Max() function, no need for a group by clause.

The aggregation scope is then the full input table.