Help!

[#1]

Good Day Qlik Community,

Can you kindly HELP me in understanding this code:

IF(IsNull(pick(

match(
valuelist( $(=concat({1}distinct chr(39) & country & chr(39),',')) )

    ,$(=concat({1}distinct chr(39) & country & chr(39),','))

    )

,$(=concat({1}distinct 'Avg({<country={'  & chr(39) & country & chr(39) & '},accountType = {"Current Account"}>}TAT)'  ,','))

)),'No Data',pick(

match(

    valuelist( $(=concat({1}distinct chr(39) & country & chr(39),',')) )

    ,$(=concat({1}distinct chr(39) & country & chr(39),','))

    )

,$(=concat({1}distinct 'Avg({<country={'  & chr(39) & country & chr(39) & '},accountType = {"Current Account"}>}TAT)'  ,','))

))

Kind Regards,

Sanjeev

[jerem1234 · Accepted solution]

It's using the pick(match()) combinations to pick an expression depending on the Country, then seeing if this is null or not. It's also using $() to evaluate some functions in order to use as part of the argument of your match and pick statements.

So what $(=concat({1}distinct chr(39) & country & chr(39),',')) is doing is is evaluating the function and returning value like:

'Canada', 'Switzerland', 'USA', 'Greece'

So when used with valuelist, creates a field with the values 'Canada', 'Switzerland', 'USA', 'Greece'.

Then with match, it goes through the list of values and picks the country:

match(valuelist('Canada', 'Switzerland', 'USA', 'Greece'), 'Canada', 'Switzerland', 'USA', 'Greece')

As an example, Canada would return a 1, Switzerland a 2, USA a 3, and Greece a 4.

Then with

$(=concat({1}distinct 'Avg({<country={'  & chr(39) & country & chr(39) & '},accountType = {"Current Account"}>}TAT)'  ,','))

this is returning something like: Avg({<country ={'Canada'}, accountType = {"Current Account"}>}TAT), Avg({<country ={'Switzerland'}, accountType = {"Current Account"}>}TAT), ...

Combined with pick():

pick(match(valuelist('Canada', 'Switzerland', 'USA', 'Greece'), 'Canada', 'Switzerland', 'USA', 'Greece'), Avg({<country ={'Canada'}, accountType = {"Current Account"}>}TAT), Avg({<country ={'Switzerland'}, accountType = {"Current Account"}>}TAT), ...)

Since the match function returns a 1 for Canada, pick will choose Avg({<country ={'Canada'}, accountType = {"Current Account"}>}TAT), ie the first formula. Switzerland would choose the next, USA would the third, and then if the formula gave a null for Greece, it would return a null.

Then since Greece would return null, it would get a value of 'NoData', the others would get their respective formulas.

Hope this helps!

[#3]

Thank you that is perfect!