Find all the columns for the maximum time.

[paulista]

I have the data as shown above , i want to find the max timestamp related to an id and corresponding activity and evt_type.

so there would be just one row for a unique id and the related fields.

attached is the data sheet

Please help me the feedback . thanks 

[zhadrakas · Accepted solution]

create a straight table.

Dimensions: 

- ID

- Activity Name

- ECT_Type

Expression:

max(Timestamp)

[paulista]

id is getting repeated , so i think this does not work for me

[zhadrakas]

thats because you have two different EVT_TYPES for that ID.

[tresesco]

May be this?

Dimension: 

- ID

Expression 1 : Activity Name

FirstSortedValue( [ Activity Name], -Timestamp)

Expression 2 : EVT Type

FirstSortedValue( EVT_Type, -Timestamp)

Expression 3:

max(Timestamp)

[paulista]

Thats what, i only want to pick the evt for that  id where max time

[paulista]

this just gives me nothing at all in the table

[manoranjan_d]

try the below script and check the details in table box

T1:
LOAD ID,
[Activity Name],
EVT_TYPE,
(TIMESTAMP) AS TIMESTAMP,
ID&TIMESTAMP AS KEY
FROM
[C:\Users\537792\Downloads\Book2.xlsx]
(ooxml, embedded labels, table is Sheet1);

D2:
LOAD
FirstSortedValue(KEY,-TIMESTAMP) AS KEY,
'Max' as flag
Resident T1
Group BY ID;

chart as straight table:

Dim as ID,activity name,evt type

exp as max({<flag={'Max'}>}TIMESTAMP)

[paulista]

thanks , can you help me how to get count of distinct events at max time please

[zhadrakas]

Choose your dimensions: 

- Activity Name

- Event Type 

i think you only want one. Just Try it out.

Expression:

count(distinct if(TIMESTAMP = aggr(NODISTINCT max(TIMESTAMP), ID, [Activity Name], EVT_TYPE), ID))