Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

long5hot
New Contributor III

Is there an alternative to "if" when matching dimensions within a table?

I have a table with three dimensions, I'll call them Card1, Card2 and Card3.  There are many, many values for each of these dimensions, but this table is typically only displayed when filters are in place to show only a very limited set of Card1 values.

For the purposes of this question here's a sample dataset:

Card1: 101, 102, 103, 104

Card2: 100, 102, 103, 105

Card3: 101, 103, 105, 106

What I want to do is show values of Card2 and Card3 that match the values of Card1.  For example..

Card1     Card2     Card3

-----------------------------

101       -         101

102       102       -

103       103       103

104       -         -      


A simple solution would be to do this with if statements ... if (Card1 = Card2, Card2) and the same for Card3. That works fine for smaller datasets, but with the scale of data I'm working with, Qlik goes off to deep space trying to find matches, and eventually fails with an error message on the table.  I have tried alternatives involving Aggr and set expressions but haven't been able to find something that works.


So my question is whether there is another way to solve this problem, or if I have to abandon it as a lost cause?


Thanks in advance

2 Replies

Re: Is there an alternative to "if" when matching dimensions within a table?

Hi,

My suggestion is that you do this in script instead of at chart level.

This will help you to minimize the calculation load.

Regards,

Re: Is there an alternative to "if" when matching dimensions within a table?

I agree with Kaushik that you should consider if a script-solution isn't the better choice. Further there are also other ways to check if the values between various fields are matching or not, for example:

rangesum(Card1 = Card2 * 1, Card1 = Card3 * 2, Card2 = Card3 * 3) as Flag

you might need some adjustments to the multiplicator and/or using another range-function like range-min/max/avg to catch all possible variations for your checking-logic and maybe wrap it in a pick(match()) or dual() or link a further table to it to get also a string-representation to your Flag-ID.

- Marcus

Community Browser