topic Re: First sorted Value for min and max date for each ID in QlikView

First sorted Value for min and max date for each ID

Nathan22 — Sat, 16 Nov 2024 01:11:11 GMT

I am very new to qlikview and trying to get my head around the first sorted value functionality.

My problem is i have 3 columns

PRIMARY ID , EVENTS , TIMESTAMP

now i want to find for each primary ID the event at max and min time. Primary id is getting repeated as an id has to go through the lifecycle and i have to find the stage at max time and min time in same table.

Re: First sorted Value for min and max date for each ID

Vegar — Wed, 26 Feb 2020 12:21:18 GMT

Does this help you:

Data: load [PRIMARY ID], EVENTS , TIMESTAMP inline [ PRIMARY ID, EVENTS , TIMESTAMP 1, Event 1.1, 2020-02-26 14:17:01 1, Event 1.3, 2020-02-26 16:19:03 1, Event 1.2, 2020-02-26 15:18:02 2, Event 2.1, 2020-02-26 17:20:04 2, Event 2.2, 2020-02-26 18:21:05 2, Event 2.3, 2020-02-26 19:22:06 2, Event 2.4, 2020-02-26 20:23:07 3, Event 3.1, 2020-02-26 21:24:08 ]; Output: LOAD [PRIMARY ID], max(TIMESTAMP) as TIMESTAMP, 'Last' as Stage Resident Data group by [PRIMARY ID]; LOAD [PRIMARY ID], 'First' as Stage, min(TIMESTAMP) as TIMESTAMP Resident Data group by [PRIMARY ID]; LEFT join LOAD * Resident Data; Drop Table Data; Exit script

It gives this output:

Re: First sorted Value for min and max date for each ID

Nathan22 — Thu, 27 Feb 2020 11:14:16 GMT

left joins really do wonders thanks for the help