topic latest row per id in QlikView

latest row per id

Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

Hope everyone is having a good day.

I would like to fetch latest row per id. One id has multiple support contracts associated with them. From these multiple rows I would like to get that row which has highest start date.

Please let me know how can I achieve that.

Any help is appreciated.

Best

Parth

Re: latest row per id

sunny_talwar — Wed, 22 Feb 2017 15:30:13 GMT

How does your raw data looks like? can you share few rows of data?

Something along these lines to should work:

Table:

LOAD id,

[start date],

....

FROM ....;

Right Join (Table)

LOAD id,

Max([start date]) as [start date],

Resident Table

Group By id;

Re: latest row per id

Anil_Babu_Samineni — Wed, 22 Feb 2017 15:32:31 GMT

Front end solution may be this

If(Id = Aggr(Max(Rowno(TOTAL)),[start date]), id)

Re: latest row per id

Wed, 22 Feb 2017 15:56:45 GMT

Hi Sunny,

Ideally I would like to create a new column called flag which identifies which row is the latest. Please see below how the data looks like:

supp_id	id	type	start date	end date	flag
1	1	gold	12/1/1990	11/30/1995	0
2	1	gold	12/1/1995	11/30/1999	0
3	1	silver	12/1/1999	11/30/2019	1
4	2	silver	8/15/2015	8/14/2016	0
5	2	gold	8/15/2016	8/14/2017	1
6	3	gold	9/1/2015	8/31/2017	1
7	4	silver	12/1/2016	11/30/2018	1
8	5	silver	1/1/2015	12/31/2016	0
9	5	gold	1/1/2016	12/31/2017	1

Please let me know if this is doable.

Thank you

Parth

Re: latest row per id

sunny_talwar — Wed, 22 Feb 2017 16:02:21 GMT

Then try like this:

Table:

LOAD id,

[start date],

....

FROM ....;

Left Join (Table)

LOAD id,

Max([start date]) as [start date],

1 as Flag

Resident Table

Group By id;

This will put 1 for max start date. In case you need 0 for other dates, you will have to do a resident load

FinalTable:

LOAD id,

[start date],

....,

If(Flag = 1, 1, 0) as Flag

Resident Table;

DROP Table Table;

Re: latest row per id

Wed, 22 Feb 2017 18:35:07 GMT

Thank you Sunny!!

Re: latest row per id

Wed, 22 Feb 2017 18:35:23 GMT

Thank you Anil!!