Incorrect join

[vchuprina]

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:

ACCTNUM	MEDNO	Sum ([Open Receivables])
Total		1120
AA	14627	495
AA	20514	260
AA	23955	55
AA	29102	310

Without Join:

ACCTNUM	MEDNO	Sum ([Open Receivables])
Total		505
AA	14627	165
AA	20514	130
AA	23955	55
AA	29102	155

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;

[jonathandienst]

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.

[vchuprina]

Hi Jonathan,

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

MEDNO	TX	INVOICE	ACCTNUM	QUANTITY	U_PRICE	DATE
14627	3	23824	CHECK	1	20	9/11/1992
14627	6	23824	CHECK	1	20	9/11/1992
14627	8	23824	CHECK	1	30	9/11/1992
14627	28	23824	CHECK	1	40	9/11/1992
14627	29	23824	CHECK	10	8	9/11/1992

[t_chetirbok]

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

[jonathandienst]

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

[t_chetirbok]

if you join with you Treat table

MEDNO	TX	INVOICE	ACCTNUM	QUANTITY	U_PRICE	DATE
14627	3	23824	CHECK	1	20	9/11/1992
14627	6	23824	CHECK	1	20	9/11/1992
14627	8	23824	CHECK	1	30	9/11/1992
14627	28	23824	CHECK	1	40	9/11/1992
14627	29	23824	CHECK	10	8	9/11/1992

txBilling table

INVOICE	Open Receivables
23824	100

you get this result

MEDNO	TX	INVOICE	ACCTNUM	QUANTITY	U_PRICE	DATE	Open Receivables
14627	3	23824	CHECK	1	20	9/11/1992	100
14627	6	23824	CHECK	1	20	9/11/1992	100
14627	8	23824	CHECK	1	30	9/11/1992	100
14627	28	23824	CHECK	1	40	9/11/1992	100
14627	29	23824	CHECK	10	8	9/11/1992	100

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