Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vchuprina
New Contributor II

Incorrect join

Hi All,

I try to join two tables, the main promble is duplicated data [Open Receivables]:

When I load data wihout join data looks correctly. I can't understand what the reason of issue

JOIN:

  

ACCTNUMMEDNOSum ([Open Receivables])
Total 1120
AA14627495
AA20514260
AA2395555
AA29102310

Without Join:

  

ACCTNUMMEDNOSum ([Open Receivables])
Total 505
AA14627165
AA20514130
AA2395555
AA29102155

Facts:

LOAD

MEDNO,

TX,

INVOICE,

ACCTNUM,

IF (IsNull(QUANTITY), 0, QUANTITY) As QUANTITY,

IF (IsNull(U_PRICE), 0, U_PRICE) As U_PRICE

Where Year(DATE)>='1994';

SQL SELECT *

FROM Treat;


Inner Join(Facts)

LOAD 

INVOICE,

If(BILL > AMTPAID,

BILL - AMTPAID,0) as [Open Receivables]

SQL SELECT *

FROM txBilling;

5 Replies
MVP
MVP

Re: Incorrect join

Please align your example with the problem script - where your value INVOICE?

It looks like this value is not unique in txBilling, so you might need a group by in your SQL statement.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vchuprina
New Contributor II

Re: Incorrect join

Hi Jonathan,

I've checked Treat and txBilling table. In txBilling table INVOICE values is not unique

MEDNOTXINVOICEACCTNUMQUANTITYU_PRICEDATE
14627323824CHECK1209/11/1992
14627623824CHECK1209/11/1992
14627823824CHECK1309/11/1992
146272823824CHECK1409/11/1992
146272923824CHECK1089/11/1992
t_chetirbok
Valued Contributor

Re: Incorrect join

can you post your data from Treat and txBilling without joining?

MVP
MVP

Re: Incorrect join

>>In txBilling table INVOICE values is not unique

Well there's your problem. Either sum the quantity and u_price values and group by the invoice #, or you will need to reconsider your model design.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
t_chetirbok
Valued Contributor

Re: Incorrect join

if you join with you Treat table

MEDNOTXINVOICEACCTNUMQUANTITYU_PRICEDATE
14627323824CHECK1209/11/1992
14627623824CHECK1209/11/1992
14627823824CHECK1309/11/1992
146272823824CHECK1409/11/1992
146272923824CHECK1089/11/1992

txBilling table

INVOICEOpen Receivables
23824100

you get this result

MEDNOTXINVOICEACCTNUMQUANTITYU_PRICEDATEOpen Receivables
14627323824CHECK1209/11/1992100
14627623824CHECK1209/11/1992100
14627823824CHECK1309/11/1992100
146272823824CHECK1409/11/1992100
146272923824CHECK1089/11/1992100

so, when your INVOICE doesn't unique in Treat it is not correct to join this table

Community Browser