Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
;
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
;
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!
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.