Pick Match

[risabhroy_03]

Can anyone please explain me how this pick match is working - 

=Pick(1+Match(-1,
(Company_Code=225 AND [Warehouse Set_Inventory]=700) or (Company_Code=225 AND left([Invoice No FG_GIT],3)='GTD') or (Company_Code=225 AND [Inventory Type]='RM\BOP-GIT-ARE 1')
or (Company_Code=225 AND [Warehouse Set_Inventory]=700 AND [Inventory Type]='RM\BOP-GIT')
,(Company_Code=225 AND [Warehouse Set_Inventory]<>700) or (Company_Code=225 AND left([Invoice No FG_GIT],3)<>'GTD') or (Company_Code=225 AND [Inventory Type]='RM\BOP-GIT-MFG')
or (Company_Code=225 AND [Warehouse Set_Inventory]<>700 AND [Inventory Type]='RM\BOP-GIT')
,Company_Code=320 AND [Country of Origin_Inventory]='IND'
,Company_Code=320 AND [Country of Origin_Inventory]<>'IND'
)
,[Selling Unit_Sales]
,'GFPL-Trading'
,'GFPL-Mfg.'
,'UOI-Trading'
,'UOI-Mfg.'
)

[Andrei_Cusnir · Accepted solution]

Hello,

The Pick() function [1], chooses an expression based on the first argument. For example: Pick(1, 1+1, 2+2, 3+3) will give you the result 2 because it will return the expression 1+1.

The Match() function [2], will compare the first parameter with all the rest and will return the position where the parameter matches. For example Match(-1, 4, 5, 6, -1) will return 4, because -1 == -1 in the 4th position of the parameters.

Therefore, you can break down your expression as:

Pick(
   1+Match(
       -1,
       EXPRESSION_1
   )
   ,DIMENSION_2
   ,DIMENSION_3
   ,DIMENSION_4
   ,DIMENSION_5
   ,DIMENSION_6
)

Now the inner Match() function, will first evaluate the huge expression EXPRESSION_1 (that has many complex comparisons) and at the end it will return a number. The expression is a bit complex so I haven't tested the possibilities but I assume that it will be either -1 (TRUE) or the number 0 (FALSE). Then if the result is -1 then Match() function will return 1 (Because it is found in the first parameter) or it will return 0 if it is not found anywhere.

Before evaluating the Pick() function, you also have 1+Match() which will give you the result of adding number 1 to any number returned from the Match() function. Based on that value, you will get back the corresponding dimension.

I am not sure about the exact use case scenario, however it seems that the only results you can get from Match(), based on your expression, are 0 or 1 (-1 is found in the result of EXPRESSION_1 or NOT). Then it means that the Pick() function will only evaluate 1+0 = 1 or 1+1 = 2, so you will only get DIMENSION_2 or DIMENSION_3.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

---
[1] https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunc...
[2] https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunc...