topic Comparing columns from 2 tables in the calculated dimension in QlikView

Comparing columns from 2 tables in the calculated dimension

Tue, 23 Oct 2012 08:14:33 GMT

Hi,

I have got 2 tables like this

Tab1:

category col1 col2 col3 tgt

A 101 102 101 XX

A 205 205 909 YY

B 299 312 355 MM

B 302 350 666 TT

C 10 25 UU

Tab2:

col1 col2 col3

101 102 101

I have to create a dimension based on the following conditions:

Join tab1 and tab2 using the join on col1, col2 and col3 and derive the tgt value. Additional condition = Category = 'A'

so essnetially tgt should be the value/result of the calculated dimension

In a SQL it roughly translates into: Select tgt from tab1 a, tab2 b where a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and a.category='A'

How do I achieve this in a calculated dimension?

Thanks

Ram

Re: Comparing columns from 2 tables in the calculated dimension

johnw — Tue, 23 Oct 2012 21:36:22 GMT

With that data model, I can't think of any way for an expression to even know if a value exists on Tab2. I also can't see any reason for Tab2 to exist, and it's forming a pointless synthetic key. I'd probably do something like this instead of loading Tab2:

LEFT JOIN (Tab1)
LOAD *
,'Y' as "Exists on Tab2?"
INLINE [
col1,col2,col3
101,102,101
299,312,355,MM
];

And then your calculated dimension could be this:

=if(category='A' and "Exists on Tab2?"='Y',tgt)

But that's not what I'd do either, because calculated dimensions have been poor performers in my experience. Instead, I'd do one more join to make this a real dimension, putting the performance penalty at reload time, not at chart display time:

LEFT JOIN (Tab1)
LOAD *
,if(category='A' and "Exists on Tab2?"='Y',tgt) as "A Tab2 tgt"
RESIDENT Tab1
;

See attached.