Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shahafei2
Creator
Creator

Pick(Match()) in Pick(Match()) or Pick(Match(aggr()))

Hi all,

I'm having an issue with the pick(match()) function

i'm loading a table as part of the script that contains two ranking fields per product

Product,Rank A,Rank B

 

I'm presenting a straight table chart

when i'm using both fields as a dimensions the expression result is correct (See expression below) - (Rank B is hidden)

But the  straight table dimension is still duplicated 

 

The expression that i'm using when i use both fields as dimensions:

pick(match([Rank B],'AA','A','B','C','D','E','F'),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_AA),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_A),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_B),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_C),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_D),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_E),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_F))

 

Could i use this expression in a way that the "Rank B" field will be hidden and that the table will present the grouped data by "Rank A" field? only 

 

Otherwise, There is a way to use the pick(match()) function with an aggregation function, if so how to use it in that case?

 

Thanks

 
1 Solution

Accepted Solutions
marcus_sommer

I think I was with my suggestion to the expression-simplification by nesting the pick(match()) within the variable a bit too quickly because the syntax-approach isn't quite correct and even more worse this kind of $-sign expansion creates an adhoc-variable which won't be calculated on row-level else globally and could therefore not work in your case.

This means you need really a separate picking of the right variable like:

pick(match([Rank B],'AA','A','B','C','D','E','F'),
    $(vABCD_Co_A), $(vABCD_Co_B), $(vABCD_Co_C), $(vABCD_Co_D), $(vABCD_Co_E), $(vABCD_Co_F))

Beside this the reason why you not got the expected results is that the matching of the variables to the rank isn't applied on the proper level. This means the dimensions within the aggr() which define the context in which the matching needs to be applied doesn't fit.
As far as your data have more then a single value within the rank B in regard to your rank A the [Rank B] within match() will return NULL - and I assume that will be quite likely in your case. Therefore needs the dimensions-list within the aggr() to be on the lowest level in which the values are definitely unique. Because you mentioned the ranks are applied to products I took Product as a dimension - but if there are sub-products or the ranking changed over any period-fields or anything else it needs to be included there.

sum(aggr(
   sum({<Line_Type={'1','2','3'}>} Daily_Avg_Sales) / 
   pick(match([Rank B],'AA','A','B','C','D','E','F'),
       $(vABCD_Co_A), $(vABCD_Co_B), $(vABCD_Co_C), $(vABCD_Co_D), $(vABCD_Co_E), $(vABCD_Co_F)),
Product, Period))

- Marcus

View solution in original post

12 Replies
sunny_talwar

May be this

Sum(Aggr(
pick(match([Rank B],'AA','A','B','C','D','E','F'),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_AA),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_A),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_B),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_C),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_D),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_E),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_F))
, [Rank A], [Rank B]))
shahafei2
Creator
Creator
Author

Unfortunately, it's not working.

I also tried to use

pick(match([Rank A],'AA','A','B','C','D','E','F'),
SUM(Aggr(sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_AA),
sum({<Line_Type={'1','2','3'>}Daily_Avg_Sales)/$(vABCD_Co_A),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_B),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_C),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_D),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_E),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_F))
,[Rank A],[Rank B])) 

marcus_sommer

I think the aggr() happens not on the proper level. I could imagine that an expression like this goes rather in the right direction:

sum(aggr(
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales / $(vABCD_Co_$(=pick([Rank B],'AA','A','B','C','D','E','F')))),
Product))

- Marcus

shahafei2
Creator
Creator
Author

Thank you,

 

But it still not working 

i also tried to replace the "Product" with "Rank A"

marcus_sommer

Please provide some more details about your datamodel, which values are within the rank-fields, some sample records and also how your objects look like and which results you expect (related to the sample records). Further what didn't work - results are NULL, 0 or wrong/unexpected in any way?

- Marcus

shahafei2
Creator
Creator
Author

Hi Marcus,

 

Thank you,

 

At the moment the object looks like 

https://prnt.sc/lq0w0d

 

For the "Sales" expression i'm using

pick(match(Rank B,'AA','A','B','C','D','E','F'),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_AA),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_A),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_B),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_C),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_D),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_E),
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales)/$(vABCD_Co_F))
 

 

When 

vABCD_Co_AA=2

vABCD_Co_A=1.7

vABCD_Co_B=1.5

vABCD_Co_C=1.3

vABCD_Co_D=1.2

vABCD_Co_E=1

vABCD_Co_F=1

 

 

My goal is to received the same result but grouped 

like you will find in the attached screenshot (I removed the "Rank B" dimension)

https://prnt.sc/lq0zwx

marcus_sommer

Depending on your datamodel you might need further/another dimensions within the aggr, too. What is returned by using this as expression:

$(vABCD_Co_$(=pick([Rank B],'AA','A','B','C','D','E','F')))
   and
sum({<Line_Type={'1','2','3'}>}Daily_Avg_Sales / $(vABCD_Co_$(=pick([Rank B],'AA','A','B','C','D','E','F'))))

and what is displayed within the expression header - after removing the expression label - if you mouseover ?

- Marcus

shahafei2
Creator
Creator
Author

The header is now displays the following only 

"

and
sum({<Line_Type={'1','2,'3'}>}Daily_Avg_Sales  /

 

"

 

(without the results for/the text of the variable)

 

and the expression return "-" for all  of the "Rank A" values

 

marcus_sommer

Does it also happens if rank B is included as dimension?