Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
diwakarnahata
Creator
Creator

Issue with join key (full outer join) - Optimization

Hi All,

I want to optimize my data model by minimizing the # of columns used, especially the columns with a lot of distinct values.

I have a scenario where, there are two tables associated with a key column say key1.

Now, QlikView by default creates a full outer join, and the join column holds all the distinct values from the two tables combined.

Is there any way i can count the distinct values from any one of the two tables using the same join key key1?

Currently, i have to create a copy of the key1 in both the tables (which is not used for joining) for the counts.

Let me know what is recommended and if my approach is correct.

Regards,

Diwakar

1 Reply
sunny_talwar

If there is a column which is unique to each of its respective tables and it is available throughout the table, then you can use that to do a distinct count. For instance if Table 1 has a column name city which is not in Table 2. Then within your text object you can write this expression: =Count(DISTINCT {<city = {'*?'}>} key1). This will only count places where city is not blank, which is available in your Table 1 only. (Table 2 will have it all blank)

You can also create your flag for each of the table in the script and use that flag in your set analysis statement (more efficient way to do it, but require manipulation in the script itself)

Table1:

LOAD xyz,

          key1

          1 as Flag1

FROM xyz;

Table2:

LOAD zzz,

          key1,

          1 as Flag2

FROM zzz;

Expression for count: =Count(DISTINCT {<Flag1 = {1}>}) key1) - > will give you distinct count of key1 from Table1

                                =Count(DISTINCT {<Flag2 = {1}>}) key1) - > will give you distinct count of key1 from Table2

HTH

Best,

S