topic Re: max text value for each IDs in QlikView

max text value for each IDs

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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

];

Re: max text value for each IDs

olivierrobin — Tue, 20 Mar 2018 09:56:27 GMT

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;

Re: max text value for each IDs

niclaz79 — Tue, 20 Mar 2018 09:58:29 GMT

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.

Re: max text value for each IDs

shiveshsingh — Tue, 20 Mar 2018 10:00:53 GMT

What's the logic for output?

Re: max text value for each IDs

shiveshsingh — Tue, 20 Mar 2018 10:01:21 GMT

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

Re: max text value for each IDs

sasiparupudi1 — Tue, 20 Mar 2018 10:06:54 GMT

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;

Re: max text value for each IDs

karan_kn — Tue, 20 Mar 2018 10:16:22 GMT

Thanks, working fine

Re: max text value for each IDs

olivierrobin — Tue, 20 Mar 2018 10:29:26 GMT

it was a pleasure

Re: max text value for each IDs

rajaxavier — Thu, 30 Apr 2020 12:26:44 GMT

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 ;