topic Re: Find all the columns for the maximum time. in QlikView

Find all the columns for the maximum time.

paulista — Sat, 16 Nov 2024 01:43:23 GMT

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

Re: Find all the columns for the maximum time.

zhadrakas — Wed, 11 Dec 2019 12:05:18 GMT

create a straight table.

Dimensions:

- ID

- Activity Name

- ECT_Type

Expression:

max(Timestamp)

Re: Find all the columns for the maximum time.

paulista — Wed, 11 Dec 2019 12:39:06 GMT

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

Re: Find all the columns for the maximum time.

zhadrakas — Wed, 11 Dec 2019 12:43:21 GMT

thats because you have two different EVT_TYPES for that ID.

Re: Find all the columns for the maximum time.

tresB — Wed, 11 Dec 2019 13:02:58 GMT

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)

Re: Find all the columns for the maximum time.

paulista — Wed, 11 Dec 2019 12:57:34 GMT

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

Re: Find all the columns for the maximum time.

paulista — Wed, 11 Dec 2019 17:21:00 GMT

this just gives me nothing at all in the table

Re: Find all the columns for the maximum time.

manoranjan_d — Wed, 11 Dec 2019 18:05:10 GMT

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)

Re: Find all the columns for the maximum time.

paulista — Thu, 12 Dec 2019 11:19:38 GMT

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

Re: Find all the columns for the maximum time.

zhadrakas — Thu, 12 Dec 2019 11:42:17 GMT

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

Re: Find all the columns for the maximum time.

paulista — Thu, 12 Dec 2019 11:59:23 GMT

count(distinct if(HDR_TIMESTAMP = aggr(NODISTINCT max(HDR_TIMESTAMP), HDR_CORR_ID), HDR_CORR_ID))

it gives me the correct count when i type this.

not when i take hdr_event_type, why would that be ?

also when i click on the value it changes to a value which is not a correct number .

Re: Find all the columns for the maximum time.

zhadrakas — Thu, 12 Dec 2019 12:08:04 GMT

with your current statement you are calculating the max(timestamp) per ID, which is not what you wanted.

You wanted the max(timestamp) per ID & Event & Activity.

So you need to aggregate over this three dimensions:

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

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

Re: Find all the columns for the maximum time.

paulista — Thu, 12 Dec 2019 12:23:41 GMT

see th table in left should guve me 34 total, but if you see at distinct events , they dont add up to 34 but much more

table on the left shows me a total of 34 but yif you add up the individual counts its much more , it should add upto 34