topic Re: How to filter latest data from table in QlikView

How to filter latest data from table

msmichael — Wed, 25 Nov 2020 16:16:04 GMT

Hi experts,

I have a table with three fields: ID, Title, LastUpdated. This table is like Title auditing table, so for each ID, I keep track the title changes and when the change is made. some sample data look like below:

ID	Title	LastUpdated
1	CRD Report	1/31/2019 16:30
1	MRD Report	12/31/2018 16:30
2	Inventory Report	2/28/2019 16:30
2	Inventory Report New	3/31/2019 16:30
3	FX Report	6/30/2019 16:30
4	Report New	3/31/2019 16:30
4	Inventory Report New	5/31/2019 16:30
4	New Portfolio	6/30/2019 16:30

I want to get the latest Title for each ID, however, I need the Title to be a dimension because I want to have a dropdown on the header of the Title field, also I'll add more expressions fields in the future. How can I do this?

For example, for above data, the result I want is,

ID	Title	LastUpdated
1	MRD Report	12/31/2018 16:30
2	Inventory Report New	3/31/2019 16:30
3	FX Report	6/30/2019 16:30
4	New Portfolio	6/30/2019 16:30

qvw attached.

Thanks,

Michael

Re: How to filter latest data from table

msmichael — Mon, 21 Oct 2019 22:27:16 GMT

correction: the result table should look like below: (sorry I don't know how to edit my post..)

ID	Title	LastUpdated
1	CRD Report	1/31/2019 16:30
2	Inventory Report New	3/31/2019 16:30
3	FX Report	6/30/2019 16:30
4	New Portfolio	6/30/2019 16:30

Re: How to filter latest data from table

MarkWillems — Tue, 22 Oct 2019 13:00:29 GMT

LEFT JOIN (TableName)

LOAD ID,Max(LastUpdated) as LastUpdated, Max(1) as LatestRec

RESIDENT TableName

Group BY ID;

Then use LatestRec=1 in your set analysis to only get the latest record.

Re: How to filter latest data from table

msmichael — Tue, 22 Oct 2019 13:10:45 GMT

Hi Mark,

thanks for your solution. However, I need the result set to be dynamic, meaning I have a calendar object and a vDate variable this is bound to the calendar, so user can change the date to get the latest Titles by the date chosen.

thanks,

Michael

Re: How to filter latest data from table

MarkWillems — Tue, 22 Oct 2019 14:29:16 GMT

I see.

Then in that case use FirstSortedValue to bring back the data you want. it's a bit heavier on calculation resources, but will work dynamically.

Re: How to filter latest data from table

Brett_Bleess — Wed, 30 Oct 2019 19:30:39 GMT

Michael, did Marks last post get everything working for you? If so, do not forget to give him credit by using the Accept as Solution button on the post(s) that helped you get things working. If you are still working on things, leave us an update.

Regards,
Brett

Re: How to filter latest data from table

Arthur_Fong — Thu, 31 Oct 2019 04:49:24 GMT

You are having issue because the LastUpdated column is in text.

Try formatting LastUpdated column to timestring format and it should work:

Refer attached qvw as reference.

Thanks and regards,

Arthur Fong