Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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 |
can you post your data from Treat and txBilling without joining?
>>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.
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