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?
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.