Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | - |
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)))
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.