Help to understand the use of pick and match function in qliksense script ?

[ashmitp869]

Spoiler

Hi ,

Please kindly help me to understand the below pick and match use in qliksense script.

LOAD
MakeDate(Year("End Date"),Month("End Date"),Day("End Date")) as ReportingDate,
pick(match("Care Type",'1','6','7','11'),'1','4','5','M','') as care_type,
1 as Measure;

SQL SELECT
End Date
"Care_Type" as "Episode of Care Type",
1 as Measure
From
Table



regards

[Vegar · Accepted solution]

pick(match("Care Type",'1','6','7','11'),'1','4','5','M','') as care_type,

Match() returns a number depending on the [Care Type] value.

If [Care Type] is 1 then Match() returns 1

If [Care Type] is 6 then Match() returns 2

If [Care Type] is 7 then Match() returns 3

If [Care Type] is 11 then Match() returns 4

If [Care Type] is any other number then Match() returns 0

In a pick(match()) you use the value from your Match() to pick the Nth value in your pick list.

If  Match() returns 1 the pick() will return 1

If  Match() returns 2 the pick() will return 4

If  Match() returns 3 the pick() will return 5

If  Match() returns 4 the pick() will return M

If  Match() returns 0 the pick() will return nothing

I hope this explanation was understandable and of help.

- Vegar 

[KD_]

Hi Vegar, thank you for the response to this issue.

I am also new to Qlik & I am trying to understand how to properly use the Match() function. Just a quick question on your answer above. I follow what is going on there, but what I don't understand is a situation where we have to, for example, use Match() in a WHERE NOT MATCH() statement in the load script. I am thinking in such a scenario, you provide the values to be matched in the Match() (e.g. WHERE NOT MATCH(State, 'TX', 'CA', 'NY') & the script only return data that correspond to the State field's rows which do NOT match the specified values. How does that work here & how does that operation differ from the example you gave. Thank you.

[Vegar]

MATCH(State, 'TX', 'CA', 'NY')  returns 1, 2 or 3 when State is  'TX', 'CA' or 'NY' and returns 0 for all other values.

This means that 

WHERE NOT MATCH(State, 'TX', 'CA', 'NY') 

is equivalent to 

WHERE MATCH(State, 'TX', 'CA', 'NY') = 0

Match returning zero for undesired values is something that I make use of in the pick(match()) solution above. Pick will only return when I have the values that I need to handle.

[KD_]

Ok, makes sense. Thanks a lot.