How to filter latest data from table

[msmichael]

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

[msmichael]

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

[MarkWillems]

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.

[msmichael]

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]

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.

[Brett_Bleess]

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

[Arthur_Fong]

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