topic Re: does association effect result in QlikView

does association effect result

surajap123 — Fri, 01 May 2015 13:01:21 GMT

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??

Re: does association effect result

Gysbert_Wassenaar — Fri, 01 May 2015 13:05:44 GMT

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

Re: does association effect result

surajap123 — Fri, 01 May 2015 13:24:33 GMT

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.

Re: does association effect result

Gysbert_Wassenaar — Fri, 01 May 2015 13:30:16 GMT

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.

Re: does association effect result

sunny_talwar — Fri, 01 May 2015 13:34:44 GMT

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

Re: does association effect result

surajap123 — Fri, 01 May 2015 13:44:48 GMT

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.

Re: does association effect result

sunny_talwar — Fri, 01 May 2015 13:48:19 GMT

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

Re: does association effect result

Gabriel — Fri, 01 May 2015 14:04:25 GMT

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.