max text value for each IDs

[karan_kn]

Please help us to set max Product value for each ID, refer screenshot for better understanding.

LOAD * INLINE [

    ID, Product

    1, Gold

    1, Silver

    1, Bronze

    2, Gold

    3, Silver

    3, Bronze

    4, Gold

    4, Silver

    4, Bronze

    5, Bronze

];

[olivierrobin · Accepted solution]

hello,

try this

p:
LOAD * INLINE [

ID, Product
1, Gold
1, Silver
1, Bronze
2, Gold
3, Silver
3, Bronze
4, Gold
4, Silver
4, Bronze
5, Bronze
];

v:
load * inline [
Product,Value
Gold,3
Silver,2
Bronze,1
]
;

left join(p)
load *
resident v;
drop table v;


res:
load ID,FirstSortedValue(Product,-Value)



resident p
group by ID;



exit script;

[niclaz79]

Hi,

This is not possible without telling Qlikview what is the highest value of Gold, Silver, Bronze.

You can do this in many ways; adding a separate table with Gold, Silver and Bronze and corresponding to values 1, 2 and 3 or in your table directly state values 1,2 and 3 and link those numbers to Gold, Silver and Bronze.

[shiveshsingh]

What's the logic for output?

[shiveshsingh]

Else you need to define the value for your products, then try!

[sasiparupudi1]

mapping_value:

Mapping Load

* Inline

[

From, To

Gold,3

Silver,2

Bronze,1

];

Test_Max:

Load

*,

ApplyMap('mapping_value',Product,0) as RatingNo;

LOAD * INLINE [

    ID, Product

    1, Gold

    1, Silver

    1, Bronze

    2, Gold

    3, Silver

    3, Bronze

    4, Gold

    4, Silver

    4, Bronze

    5, Bronze

];

Left Join(Test_Max)

Load ID,

Max(RatingNo) as MaxRatingNo

Resident Test_Max

Group by ID;

Final:

NoConcatenate Load

ID,Product

Resident

Test_Max

WHERE

RatingNo=MaxRatingNo;

DROP TABLE Test_Max;

[karan_kn]

Thanks, working fine

[olivierrobin]

it was a pleasure

[rajaxavier]

Tab1:
load * inline [
Product,Value
Gold,3
Silver,2
Bronze,1
];

Right join

Tab :
load * inline [
ID, Product

1, Gold

1, Silver

1, Bronze

2, Gold

3, Silver

3, Bronze

4, Gold

4, Silver

4, Bronze

5, Bronze

];

Right Join

Tab2 :
load ID, max(Value) as Value Resident Tab1 Group by ID ;