Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Asuod_
Creator
Creator

How to Filter Table to show ID row with Max Date

Hello, 

I cant figure out how to filter a table based on if they have the same ID and Category then only return the row with the max timestamp_value. I am trying to accomplish with a set analysis in the table not the data load editor and want to keep the rows that have a null timestamp. Any help is appreciated thank you!

 Original Table:

ID Category value timestamp_value
0 Clothing C 5/15/2024 8:09:35 PM
0 Clothing L 5/15/2024 8:21:37 PM
1 Tools R 5/15/2024 8:09:35 PM
2 Furniture A11 -


Goal Table:

ID Category value timestamp_value
0 Clothing L 5/15/2024 8:21:37 PM
1 Tools R 5/15/2024 8:09:35 PM
2 Furniture A11 -
Labels (1)
2 Replies
HirisH_V7
Master
Master

Hi Do below,

Script -

Convert your timestamp into num format and replace nulls with 1.

 

If(isnull(Timestamp(Timestamp#(timestamp_value,'MM/DD/YYYY h:mm:ss TT'))),1,
Timestamp(Timestamp#(timestamp_value,'MM/DD/YYYY h:mm:ss TT'))
) as timestamp_value_Converted

 

UI -

Dimension -   ID, Category

Expressions Below ,

 

Value 

=FirstSortedValue(value,-timestamp_value_Converted)

Time Stamp 

If(Max(timestamp_value_Converted)='1','-',Timestamp(Max(timestamp_value_Converted)))

 

HirisH_V7_0-1715846556849.png

 

HirisH
Firefly_cam
Partner - Contributor III
Partner - Contributor III

If you apply some unique row identifier to your table (global_ID), you'll be able to achieve this via set analysis

 

Load
RowNo() as global_ID,
ID,
Category,
value,
if(timestamp_value='',Null(),timestamp_value) as timestamp_value

inline [
ID,	Category,	value,	timestamp_value
0,	Clothing,	C,	5/15/2024 8:09:35 PM
0,	Clothing,	L,	5/15/2024 8:21:37 PM
1,	Tools,	R,	5/15/2024 8:09:35 PM
2,	Furniture,	A11];
=Only({<global_ID={"=aggr(Max(TOTAL<ID> alt(timestamp_value,0))=alt(timestamp_value,0), ID, timestamp_value)"}>} value)

 

Screenshot 2024-05-20 at 16.59.15.png

Also be advised to check the edge case when there are multiple rows with same IDs and same timestamp_value. 

Regards, Roman