Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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