topic How to Filter Table to show ID row with Max Date in App Development

How to Filter Table to show ID row with Max Date

Asuod_ — Wed, 15 May 2024 21:33:43 GMT

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	-

Re: How to Filter Table to show ID row with Max Date

HirisH_V7 — Thu, 16 May 2024 08:04:01 GMT

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)))

Re: How to Filter Table to show ID row with Max Date

Firefly_cam — Mon, 20 May 2024 11:35:37 GMT

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)

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