Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

Find all the columns for the maximum time.

Capture.PNG

 

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 

 

1 Solution

Accepted Solutions
Highlighted
Valued Contributor II

Re: Find all the columns for the maximum time.

create a straight table.

Dimensions: 

- ID

- Activity Name

- ECT_Type

Expression:

max(Timestamp)

test_max.png

View solution in original post

12 Replies
Highlighted
Valued Contributor II

Re: Find all the columns for the maximum time.

create a straight table.

Dimensions: 

- ID

- Activity Name

- ECT_Type

Expression:

max(Timestamp)

test_max.png

View solution in original post

Highlighted
New Contributor III

Re: Find all the columns for the maximum time.

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

Highlighted
Valued Contributor II

Re: Find all the columns for the maximum time.

thats because you have two different EVT_TYPES for that ID.

Highlighted
MVP
MVP

Re: Find all the columns for the maximum time.

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)

Highlighted
New Contributor III

Re: Find all the columns for the maximum time.

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

Highlighted
New Contributor III

Re: Find all the columns for the maximum time.

this just gives me nothing at all in the table

Highlighted
Contributor III

Re: Find all the columns for the maximum time.

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)

 

Highlighted
New Contributor III

Re: Find all the columns for the maximum time.

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

Highlighted
Valued Contributor II

Re: Find all the columns for the maximum time.

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