Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
micheledenardi
Valued Contributor

Find max Value

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
11.0.1
13.2.9
12.13.4
11.9.6
13.2.18
23.0.12
22.0.2
23.1.0
22.13.13
1 Solution

Accepted Solutions
MVP
MVP

Re: Find max Value

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;

Capture.PNG

14 Replies

Re: Find max Value

create straight table

Dimension:

Program

Expression:

maxstring(Version)

micheledenardi
Valued Contributor

Re: Find max Value

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.

1.png

antoniotiman
Honored Contributor III

Re: Find max Value

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

micheledenardi
Valued Contributor

Re: Find max Value

Thanks Antonio,

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

2.png

Re: Find max Value

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

Output you get this

Output.PNG

Re: Find max Value

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

micheledenardi
Valued Contributor

Re: Find max Value

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)

3.png

MVP
MVP

Re: Find max Value

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;

Capture.PNG

Community Browser