topic Re: Find max Value in QlikView

Find max Value

micheledenardi — Mon, 26 Jan 2026 18:19:17 GMT

hi experts,

i have a table structured as below and i want to find the greatest version for each program, so for program 1 the greatest(leatest) version is 3.2.9 and for program 2 is 3.1.0.

How can i achieve this result ?

Program	Version
1	1.0.1
1	3.2.9
1	2.13.4
1	1.9.6
1	3.2.18
2	3.0.12
2	2.0.2
2	3.1.0
2	2.13.13

Re: Find max Value

Kushal_Chawda — Wed, 06 Sep 2017 09:22:27 GMT

create straight table

Dimension:

Program

Expression:

maxstring(Version)

Re: Find max Value

micheledenardi — Wed, 06 Sep 2017 09:33:36 GMT

Thanks Kushal,

this is the solution that i have tried first but as you can see below, it doesn't work (max version is 2.13.3).

I would like to reach the result during load script.

Re: Find max Value

antoniotiman — Wed, 06 Sep 2017 09:34:16 GMT

Hi Michele,

maybe like this

LOAD Program,Version,
Num#(SubField(Version,'.',1)*100)+Num#(SubField(Version,'.',2)*10)+Num#(SubField(Version,'.',-1)) as NumericVersion

From

and in Table

Expression : FirstSortedValue(Version,-NumericVersion)

This because Version like 13.1.1

Regards,

Antonio

Re: Find max Value

micheledenardi — Wed, 06 Sep 2017 09:49:16 GMT

Thanks Antonio,

but also your solution doesn't work....

Re: Find max Value

its_anandrjs — Wed, 06 Sep 2017 09:52:23 GMT

Check this to try will it worked for you

LOAD *,PurgeChar(Version,'.') as ModiVersion;
LOAD * Inline
[
Program,Version
1,   1.0.1
1,   3.2.9
1,   2.13.4
1,   1.9.6
1,   3.2.18
2,   3.0.12
2,   2.0.2
2,   3.1.0
2,   2.13.13
];

On Straight Chart

Dim:- Program

Expression:- MaxString(ModiVersion)

Re: Find max Value

its_anandrjs — Wed, 06 Sep 2017 09:55:17 GMT

Output you get this

Re: Find max Value

Kushal_Chawda — Wed, 06 Sep 2017 09:57:13 GMT

don't you think.3.2.18 is greatest version for program 1?

Re: Find max Value

micheledenardi — Wed, 06 Sep 2017 09:58:37 GMT

Hi Anaud,

Your solution doesn't work (program 2 version is wrong) and i want to display the version with dots (3.2.18 and not 3218)

Re: Find max Value

tresB — Wed, 06 Sep 2017 10:00:20 GMT

I guess, this should work:

Input:

Load *,

SubField(Version,'.',1) as V1,

SubField(Version,'.',2) as V2,

SubField(Version,'.',3) as V3 Inline [

Program, Version

1, 1.0.1

1, 3.2.9

1, 2.13.4

1, 1.9.6

1, 3.2.18

2, 2.0.2

2, 2.13.13

2, 2.9.1

];

NoConcatenate

Output:

LOAD

Program,

LastValue(Version) as Version2

Resident Input Group By Program Order By Program, V1, V2, V3;

DROP Table Input;

Re: Find max Value

antoniotiman — Wed, 06 Sep 2017 10:00:28 GMT

Why not ?

See Attachment.

Re: Find max Value

micheledenardi — Wed, 06 Sep 2017 10:01:44 GMT

Yes, 3.2.18 is the greatest version for program 1 but the greatest version for program 2 is 3.1.0 and not 21313 (2.13.13)

Re: Find max Value

marcus_malinow — Wed, 06 Sep 2017 10:01:57 GMT

Try this:

LOAD Program,

Dual(Version, subfield(Version, '.', 1) & num(subfield(Version, '.', 2), '00') & num(subfield(Version, '.', 3), '00')) as Version

;

LOAD * Inline

[

Program,Version

1, 1.0.1

1, 3.2.9

1, 2.13.4

1, 1.9.6

1, 3.2.18

2, 3.0.12

2, 2.0.2

2, 3.1.0

2, 2.13.13

];

Re: Find max Value

Kushal_Chawda — Wed, 06 Sep 2017 10:38:18 GMT

or simply

Data:

LOAD *,

num(SubField(Version,'.',1),'00') & num(SubField(Version,'.',2),'00') & num(SubField(Version,'.',3),'00') as Number

Inline [

Program, Version

1, 1.0.1

1, 3.2.9

1, 2.13.4

1, 1.9.6

1, 3.2.18

2, 3.0.12

2, 2.0.2

2, 3.1.0

2, 2.13.13

3, 2.7.1

3, 2.13.3];

Create straight table

Dimension:

Program

Expression:

=FirstSortedValue(Version,-Number)

Re: Find max Value

its_anandrjs — Wed, 06 Sep 2017 10:43:43 GMT

Ok then one solution is this also for you kindly check once

MainTab:
LOAD *,PurgeChar(Version,'.') as ModiVersion;
LOAD * Inline
[
Program,Version
1, 1.0.1
1, 3.2.9
1, 2.13.4
1, 1.9.6
1, 3.2.18
2, 3.0.12
2, 2.0.2
2, 3.1.0
2, 2.13.13
];

MaxVersion:
LOAD
Program,
Max(ModiVersion) as MaxVer
Resident MainTab Group By Program;

MaxVerFinal:
Mapping LOAD
MaxVer,'Max Ver' as MaxFlag
Resident MaxVersion;
DROP Table MaxVersion;

NoConcatenate
Final:
LOAD
*,ApplyMap('MaxVerFinal',ModiVersion,'None') as MaxVerField
Resident MainTab;

DROP Field ModiVersion;
DROP Table MainTab;
EXIT Script;

OutPut:-

See attached also