Qlik Community

Ask a Question

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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
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
Kush
MVP
MVP

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

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

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

Kush
MVP
MVP

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

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