Qlik Sense: Showing only the first value...FirstSorted or Above/Below?

[QlikChick]

Greetings! 

Looking for some much needed help with a chart in Qlik Sense. Here's what I have:

Client	Products
111004	Basic
111007	Complete
111007	Essential
111007	Basic
114027	Complete
114027	Complete
114027	Essential
114027	Basic
114027	Basic

I would like to display unique values for the client. Basically  I need to know what the highest level of product a  unique client has with us (multiple clients because of multiple locations). Complete being the top, secondary is Essential, then Basic. Here's what the end result would be in my ideal table:

Client	Products
111004	Basic
111007	Complete
114027	Complete

Help would be greatly appreciated...I'm not sure how to use FirstSorted Value in this case, or Above/Below...

Thanks!

Jenny

[kaushiknsolanki · Accepted solution]

Hi,

The best way is to create a flag in Script like if Basic then 1, if Essentials then 2, if complete then 3 etc.

Ans then you can write advanced set analysis to get the right data.

The expression in this case will be

Maxstring({<Flag = p({<Flag = {'=Aggr(Max(Flag),Client)'}>})>}Products)

If you dont want to do that you can create a table with client and products as dimension and expression as

Aggr(Max(If(Products = 'Basic',1,If(Products='Essential',2,If(Products='Complete',3)))),Client)

[QlikChick]

Thanks, Kaushik! 

I have altered the script as you mentioned, and now I have this:

Client	Products	Rank
111004	Basic	1
111007	Complete	3
111007	Essential	2
111007	Basic	1
114027	Complete	3
114027	Complete	3
114027	Essential	2
114027	Basic	1
114027	Basic	1

When I try to add this part below, I get an error:

Maxstring({<Flag = p({<Flag = {'=Aggr(Max(Flag),Client)'}>})>}Products)

I am adding it as a column in my straight table. It tells me the expression is "OK", but I do get an error and it does not work on the visualization.

Thanks for the reply BTW, any further help is greatly appreciated!

[kaushiknsolanki]

Hi,

Because your new field name is Rank, your expression should replace the Flag with Rank keyword. so the expression should be

Maxstring({<Rank= p({<Rank= {'=Aggr(Max(Rank),Client)'}>})>}Products)