Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
msmichael
Creator
Creator

How to filter latest data from table

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:

IDTitleLastUpdated
1CRD Report1/31/2019 16:30
1MRD Report12/31/2018 16:30
2Inventory Report2/28/2019 16:30
2Inventory Report New3/31/2019 16:30
3FX Report6/30/2019 16:30
4Report New3/31/2019 16:30
4Inventory Report New5/31/2019 16:30
4New Portfolio6/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, 

IDTitleLastUpdated
1MRD Report12/31/2018 16:30
2Inventory Report New3/31/2019 16:30
3FX Report6/30/2019 16:30
4New Portfolio6/30/2019 16:30

 

qvw attached.

 

Thanks,

Michael

6 Replies
msmichael
Creator
Creator
Author

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

IDTitleLastUpdated
1CRD Report1/31/2019 16:30
2Inventory Report New3/31/2019 16:30
3FX Report6/30/2019 16:30
4New Portfolio6/30/2019 16:30
MarkWillems
Contributor III
Contributor III

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.

Hey, Please don't forget to add kudos, like or mark as a solution if my reply has helped you at all! 🙂
msmichael
Creator
Creator
Author

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

MarkWillems
Contributor III
Contributor III

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.

Hey, Please don't forget to add kudos, like or mark as a solution if my reply has helped you at all! 🙂
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Arthur_Fong
Partner
Partner

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

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

MC.PNG

Refer attached qvw as reference.

Thanks and regards,

Arthur Fong