topic Load highest version number per ID in QlikView

Load highest version number per ID

Fri, 13 May 2011 13:16:30 GMT

Hello QVer,
please see attached QVW.

I want to load a source table with a [Dokument]-ID field; each document has one or more versions [Vs] mostly multiple depending on items per [Dokument]-ID.
Now I need only those rows which have the highest version number per [Dokument]-ID.
First step I'm sorting [Vs] per [Dokument] but how to pick out only those [Dokument]-IDs with the highest version number?

Any idea to solve that in script or ...? Thanks.
Greetings dj_skbs

Re: Load highest version number per ID

pat_agen — Fri, 13 May 2011 13:36:28 GMT

hi,

here is a solution via the script. the code below will add a field called Latestversio to your dataset. This has a value of "1" iif the document ios the latest version. You can then use to filer or simply eliminate the older versions.

LABOR:
LOAD * INLINE [
    Dokument, Vs, Leistung, Text, Wert, Einheit, Normal
    10005019585, 0, LB000063, Quick, 100, %, 70 - 130
    10005019585, 1, LB000063, Quick, 100, %, 70 - 130
    10005019585, 2, LB000063, Quick, 100, %, 70 - 130
    10005025652, 1, LB000503, Hämatokrit, 26.2, %, 34.1 - 44.9
    10005019585, 0, LB000506, MCHC, 29.8, g/dl, 32.2 - 36.5
    10005019585, 1, LB000506, MCHC, 29.8, g/dl, 32.2 - 36.5
    10005019585, 2, LB000506, MCHC, 29.8, g/dl, 32.2 - 36.5
    10005019585, 1, LB000501, Erythrocyten, 3.42, 10e6/µl, 3.93 - 5.22
    10005023939, 1, LB000503, Hämatokrit, 31.0, %, 34.1 - 44.9
    10005025652, 1, LB000506, MCHC, 32.1, g/dl, 32.2 - 36.5
    10005023939, 1, LB000506, MCHC, 32.3, g/dl, 32.2 - 36.5
    10005019585, 0, LB000503, Hämatokrit, 32.6, %, 34.1 - 44.9
    10005019585, 1, LB000503, Hämatokrit, 32.6, %, 34.1 - 44.9
    10005019585, 2, LB000503, Hämatokrit, 32.6, %, 34.1 - 44.9
    10005025652, 0, LB000039, Ges.Eiweiß i.P., 57.9, g/l, 64 - 83
    10005025652, 1, LB000039, Ges.Eiweiß i.P., 57.9, g/l, 64 - 83
    10005023939, 0, LB000039, Ges.Eiweiß i.P., 61.3, g/l, 64 - 83
    10005023939, 1, LB000039, Ges.Eiweiß i.P., 61.3, g/l, 64 - 83
    10005019585, 0, LB000443, MPV, 8.6, fl, 9.4 - 12.5
    10005019585, 1, LB000443, MPV, 8.6, fl, 9.4 - 12.5
    10005019585, 2, LB000443, MPV, 8.6, fl, 9.4 - 12.5
    10005023939, 0, LB000443, MPV, 8.7, fl, 9.4 - 12.5
    10005023939, 1, LB000443, MPV, 8.7, fl, 9.4 - 12.5
    10005025652, 0, LB000443, MPV, 9.0, fl, 9.4 - 12.5
    10005025653, 3, LB000443, MPV, 9.0, fl, 9.4 - 12.5
    10005023939, 0, LB000504, MCV, 92.5, fl, 80 - 101
    10005023939, 1, LB000504, MCV, 92.5, fl, 80 - 101
    10005025652, 0, LB000504, MCV, 93.2, fl, 80 - 101
    10005025653, 3, LB000504, MCV, 93.2, fl, 80 - 101
    10005019585, 0, LB000504, MCV, 95.3, fl, 80 - 101
    10005019585, 1, LB000504, MCV, 95.3, fl, 80 - 101
    10005019585, 2, LB000504, MCV, 95.3, fl, 80 - 101
    10005020810, 0, LB000188, HIV-Ak, negativ, , negativ
    10005020810, 0, LB000191, HBs-Ag, negativ, , negativ
    10005020810, 0, LB000196, HCV-Ak, negativ, , negativ
    10005020810, 1, LB000188, HIV-Ak, negativ, , negativ
    10005020810, 1, LB000191, HBs-Ag, negativ, , negativ
    10005020810, 1, LB000196, HCV-Ak, negativ, , negativ
];

LatestVersions:
load Dokument,
max(Vs) as LatestVersion
resident LABOR
group by Dokument;

LABOR:
join (LABOR) load
Dokument as Dokument,
LatestVersion as Vs,
1 as LatestVersion
resident LatestVersions;

drop table LatestVersions;

hope this helps.

Re: Load highest version number per ID

Fri, 13 May 2011 14:26:39 GMT

Hi,
thank you.
But this is not the solution corresponding to the expected result in the attached QVW.

Per [Dokument]-ID there can be different amounts of versions [Vs] consisting of one or more rows of values and I only need those rows which have the highest
(latest):
When an ID has rows with 1,3 versions I need those with 3;
when an ID has rows with 3 I need these;
when an ID has rows with 0,1,2 I need those with 2;
etc.

In conclusion:
I want to load only those rows of an [Dokument]-ID which have the highest (latest) version number.

Any idea to solve that? Thanks.
dj_skbs

Re: Load highest version number per ID

pat_agen — Fri, 13 May 2011 14:35:12 GMT

hi,

did you try the code?

i got the result who were looking for - see attached snapshot

and here is the qvw.

did I misunderstand something?

Load highest version number per ID

Fri, 13 May 2011 15:10:18 GMT

You could use something like...

if([Version]=max(total <[Document ID]> [Version]), code here

Pat's solution also looks good

Firstsortedvalue( would probably work as well but I never use that function so I can't say.

Load highest version number per ID

Mon, 16 May 2011 08:51:33 GMT

Hello,

sorry "trent.jones" but I didn't succeed to implement Your ideas.

Otherwise "pat.agen's" solution works when I tested it again; may be the weekend coming up last friday I overlooked that solution.

Thanks.

dj_skbs