Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading only the most recent date from the date field

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

;

View solution in original post

3 Replies
swuehl
MVP
MVP

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

;

Not applicable
Author

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!

swuehl
MVP
MVP

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.