Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
create straight table
Dimension:
Program
Expression:
maxstring(Version)
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.
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
Thanks Antonio,
but also your solution doesn't work....
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)
Output you get this
don't you think.3.2.18 is greatest version for program 1?
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)
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;