Comparing columns from 2 tables in the calculated dimension

[#1]

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

[johnw]

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.