does association effect result

[surajap123]

Hi Experts,

I am pulling the data from source database and also many tables are loaded. I am mainly facing issue with data loaded from orders table. 

When i use the below expression in a text object i get value as 45 in final dashboard.

Inorder to test, I loaded only orders.qvd into a qlikview document and the below expression gives result 99.

count(distinct orderID)

In final dashboard my orders table no filteration condition, however it is linked to 3 different tables using orderID field. This linking is done using LEFT KEEP(ordersTbl)

I am wondering, does the connection with other tables effect the output of the key field??

[Gysbert_Wassenaar]

LEFT KEEP will remove the records from the right side table that don't have matching key values in the left side table.

[surajap123]

Hi Gysbert,

Thanks for the response. I knew that LEFT keep reduce rows from the right table, but i am counting key field, so i expect all the key values to be present in the left table, then why the output of the expression is 45, instead of 99.

[Gysbert_Wassenaar]

On a key field you can only use count(distinct ...), not count( ... ). That will give you the total of distinct key values over both tables. That number will be correct. If you think it should be something else you'll have to debug your script.

[sunny_talwar]

If your ordersTbl has only 45 orderID you will reduce the orderID from your orders.qvd because of the LEFT KEEP. What exactly are your trying to achieve by doing a LEFT KEEP, may be you need a RIGHT KEEP if order is your FACT Table here.

Best,

Sunny

[surajap123]

Hi Gysbert,

Thanks for the info. I will get back after some investigation.

@sunindia: My orderTbl is at the beginning of the script, so i am using LEFT KEEP(orderTbl) to reduce the rows in other table. So i think my LEFT KEEP is correct.

[sunny_talwar]

You may be right, since I hardly know your data and its structure. But just because it is loaded before everything won't necessarily mean that you will have to use LEFT KEEP. You need to check which one is your FACT TABLE (meaning contain the all your order no.). If orderTbl contain all the orderid then you are right.

Best,

Sunny

[Gabriel]

Hi,

You probably have Null values in 1 of the table.

Just use NullCount(orderID) function to figure  out which of  the table has null.