Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
micheledenardi
Specialist II
Specialist II

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
Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

View solution in original post

14 Replies
Kushal_Chawda

create straight table

Dimension:

Program

Expression:

maxstring(Version)

micheledenardi
Specialist II
Specialist II
Author

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
antoniotiman
Master III
Master III

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
Specialist II
Specialist II
Author

Thanks Antonio,

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

2.png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
its_anandrjs

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)



its_anandrjs

Output you get this

Output.PNG

Kushal_Chawda

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

micheledenardi
Specialist II
Specialist II
Author

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
tresesco
MVP
MVP

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