Pick match not working as expected

[NicolasAimain]

Dear all,

I encounter an unepxected behaviour using pick match

we have two not linked table, 1 factable, 1 technical table with one unique Dimension

I have a calculation with set anaysis to look at previous year

• if I put calculation alone in a table with all dimension, including the technical one, everything works ✅
• if I put the same calculation in a pick match which test the Technical dimension, the calculation doesn't work ❌ 

Does anybody can explain why this problem occurs and it if there is a solution to solve it ?

Here are the sample data

LOAD * Inline [
Dim1
GROUP1
];

LOAD * Inline [
source total_demand type_year year
SOURCE1 2 current_year 2024
SOURCE1 1 current_year 2024
SOURCE2 3 current_year 2023
SOURCE2 3 last_year 2023
SOURCE2 1 current_year 2023
SOURCE2 1 last_year 2023
] (delimiter is '\t');

and here are my two measures

=pick(match(Dim1,'GROUP1'),(sum({< year={2023},type_year={'last_year'}>}total_demand)))

=sum({< year={2023},type_year={'last_year'}>}total_demand)

Also please find atteached the sample qvf

[MatheusC · Accepted solution]

Hi, @NicolasAimain 

Maybe bypassing the filter

Year=

see it like this

pick({<year=>} match(Dim1,'GROUP1'),(sum({< year={2023},type_year={'last_year'},year=>}total_demand)))

Regarts,
Matheus

[marcus_sommer]

It's not really clear for me what you are trying to achieve. But IMO your approach of calculating something against two not linked data-set is problematic.

Without digging in your application I think your first method is working because Qlik creates a cartesian product between both data-sets on the dimensional level and the if-loop-conditions in the expressions are reducing it again to the wanted/possible combinations. Your second method doubles the cartesian logic because it happens on the dimensional level as well as within the expression-level and both levels aren't matching against each other.

Even with rather small data-sets you could easily crash an application or the server by allowing cartesian logic within a data-set. Beside this risks it's often hard to get the wanted results with such methods. Therefore I suggest to avoid it.

In my experience there is only one sensible use-case for independent data-sets respectively island-tables and this to use them as navigation, for example to have an independent year-field like:

t: load 2019 + recno() as YearSelect autogenerate 4;

and then an UI expression may look like:

sum({< MasterCalendarYear = p(YearSelect)>} Value)

and then the selection from the island-tables controls the possible values of the origin data-set.

If the last isn't what you want to achieve you will need to adjust your data-model to create valide associations between the data.   

[vinieme12]

make it simpler by Picking Group1 and Group2 using a dual field or an alternate numeric field

LOAD * Inline [
Dim1,Dim1_so
GROUP1,1

GROUP2,2
];

Then try below

=Pick(Dim1_so , sum({< year={2023},type_year={'last_year'}>}total_demand) , ExpressionforGroup2 )

[NicolasAimain]

Thanks @MatheusC your solution works ... in the exemple I provided 🥲 (I just changed the {<year=>} by a {1} as we have in fact several field)

unfortunately, it doesn't solve our problem as we have a way more complicated apps (which I don't know well), I will investigate and probably open a new question

@marcus_sommer we use this isolate table to create distinct calculation for each cells of table like in P&L calculation see the small exemple below .... that worked well in many apps but not in this one

Column A, Column B

Var A Sum(X) Max(Z)

Var B COunt(Y) Min(W)

[marcus_sommer]

I wouldn't tend to such an approach because it means to associate data within the UI. IMO this kind of work belonged to the data-model respectively to distribute all information to appropriate dimensional layer and in the end a single and simple expression like sum(VALUE) is enough to get all needed views.