topic Re: INNER JOIN in QlikView

INNER JOIN

thanhphongle — Thu, 09 Nov 2017 14:52:17 GMT

Hello Community,

I have a problem with the output of my data. I have 3 tables and conntected them with an INNER JOIN.

But somehow when I

sum(price)

count(serial_no)

QV returns me 117.000 and 150

I was actually expecting 39.000 and 50

I understand that the QV multiplies the value with 3 cos I have an INNER JOIN between the tables contract/history and contract/assets.

But is it somehow possible to have a JOIN FUNCTION where

sum(price) = 39.000

count(serial_no) = 50

without multiplying it with 3?

I attached my qv file and my tables

Hope someone can help me out here.

Re: INNER JOIN

micheledenardi — Thu, 09 Nov 2017 15:00:43 GMT

This is because when you join "history" tab with the other tables the record triplicates.

If you want to have only one final table you have three options:

1) change your keys between "history" and "assets".

2) take only one record for each "contract_no".

3) do your inner joins but on your charts use sum(price)/3 and count(serial_no)/3 as expressions.

hope it helps

Re: INNER JOIN

Or — Thu, 09 Nov 2017 15:05:18 GMT

What you are describing seems like the correct behavior in this case. As stated in Michele's post above, you triplicate the record when you inner join the N:1 table to the source table. If you don't want this behavior, don't join the tables - QlikView doesn't need the join.

Re: INNER JOIN

thanhphongle — Thu, 09 Nov 2017 15:22:44 GMT

I just removed the JOINS. Now it perfectly works. Thanks very much !!