topic Re: Loading only records with largest revision number in QlikView

Loading only records with largest revision number

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a set of records with revision number as shown below :

SN	REQ_NO	REV_NO	Department
1	N01	1	Dept 1
2	N01	2	Dept 1
3	N02	1	Dept 2
4	N02	2	Dept 2
5	N02	3	Dept 2
6	N02	4	Dept 3
7	N03	1	Dept 3
8	N04	1	Dept 5
9	N05	1	Dept 10
10	N05	2	Dept 10
11	N05	3	Dept 5
12	N06	1	Dept 6

How do I load only the records with the largest revision number, i.e. only the following records are loaded into a table ?

SN	REQ_NO	REV_NO	Department
2	N01	2	Dept 1
6	N02	4	Dept 3
7	N03	1	Dept 3
8	N04	1	Dept 5
11	N05	3	Dept 5
12	N06	1	Dept 6

Re: Loading only records with largest revision number

tamilarasu — Tue, 01 Nov 2016 02:07:36 GMT

Hi CheongWeei,

You can use inner join in this case.

Data:
LOAD * INLINE [
SN, REQ_NO, REV_NO, Department
1, N01, 1, Dept 1
2, N01, 2, Dept 1
3, N02, 1, Dept 2
4, N02, 2, Dept 2
5, N02, 3, Dept 2
6, N02, 4, Dept 3
7, N03, 1, Dept 3
8, N04, 1, Dept 5
9, N05, 1, Dept 10
10, N05, 2, Dept 10
11, N05, 3, Dept 5
12, N06, 1, Dept 6
];

Inner Join (Data)
Load REQ_NO, Max(REV_NO) as REV_NO
Resident Data Group by REQ_NO;

Result:

Re: Loading only records with largest revision number

Tue, 01 Nov 2016 05:16:04 GMT

Nagaraj gave good example

Re: Loading only records with largest revision number

marjan_it — Tue, 01 Nov 2016 06:19:15 GMT

you can use it in expression like this:

if(not IsNull( aggr(max(REV_NO),REQ_NO)),aggr(max(REV_NO),REQ_NO))

Re: Loading only records with largest revision number

Anonymous — Tue, 01 Nov 2016 10:46:02 GMT

Hi Nagaraj,

I just noted that my data has duplicate records . That is after loading only records with the largest revision number, there are still duplicate.

Sample of the original data

SN	REQ_NO	REV_NO	Department
1	N01	1	Dept 1
2	N01	2	Dept 1
3	N01	2	Dept 1
4	N02	1	Dept 2
5	N02	2	Dept 2
6	N03	1	Dept 2

After doing an inner join, I get this.

SN	REQ_NO	REV_NO	Department
2	N01	2	Dept 1
3	N01	2	Dept 1
5	N02	2	Dept 2
6	N03	1	Dept 2

What I would like to have is this

SN	REQ_NO	REV_NO	Department
2	N01	2	Dept 1
5	N02	2	Dept 2
6	N03	1	Dept 2

Re: Loading only records with largest revision number

tamilarasu — Tue, 01 Nov 2016 16:55:59 GMT

Hello CheongWeei,

Add another inner join like below.

Data:
LOAD * INLINE [
SN, REQ_NO, REV_NO, Department
1, N01, 1, Dept 1
2, N01, 2, Dept 1
3, N01, 2, Dept 1
1, N01, 2, Dept 1
4, N02, 1, Dept 2
5, N02, 2, Dept 2
6, N03, 1, Dept 2
];

Inner Join (Data)
Load REQ_NO, Max(REV_NO) as REV_NO
Resident Data Group by REQ_NO;

Inner Join (Data)
Load REQ_NO, Min(SN) as SN
Resident Data Group by REQ_NO;

Re: Loading only records with largest revision number

Anonymous — Wed, 02 Nov 2016 00:47:25 GMT

Nagaraj,

Got it ! Thanks

What if the records are exactly the same, ie, there is no SN field

REQ_NO, REV_NO, Department
N01, 1, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N02, 1, Dept 2
N02, 2, Dept 2
N03, 1, Dept 2

Re: Loading only records with largest revision number

sunny_talwar — Wed, 02 Nov 2016 01:44:15 GMT

Is this the required output?

Using this script:

Table:

LOAD REQ_NO,

Max(REV_NO) as REV_NO,

FirstSortedValue(DISTINCT Department, -REV_NO) as Department

Group By REQ_NO;

LOAD * Inline [

REQ_NO, REV_NO, Department

N01, 1, Dept 1

N01, 2, Dept 1

N02, 1, Dept 2

N02, 2, Dept 2

N03, 1, Dept 2

];

Re: Loading only records with largest revision number

tamilarasu — Wed, 02 Nov 2016 06:57:57 GMT

CheongWeei,

You can use Sunny's solution or again inner join

Data:
Load Distinct * Inline [
REQ_NO, REV_NO, Department
N01, 1, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N02, 1, Dept 2
N02, 2, Dept 2
N03, 1, Dept 2
];

Inner Join (Data)
Load REQ_NO, Max(REV_NO) as REV_NO
Resident Data Group by REQ_NO;

Result:

Re: Loading only records with largest revision number

Anonymous — Thu, 03 Nov 2016 22:51:50 GMT

Thanks !