Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I've a problem with one table, data all time duplicated. For example company AA have following data:
MEDNO | ACCTNUM | INVOICE | BILL | DATE | AMTPAID |
---|---|---|---|---|---|
14627 | AA | 55552 | 165 | 11/6/1996 | 0 |
20514 | AA | 56897 | 130 | 1/2/1997 | 0 |
29102 | AA | 56970 | 155 | 1/6/1997 | 0 |
23955 | AA | 9700500 | 55 | 3/18/1997 | 0 |
In report I should have following (Sum equal 505):
Account | Current | 1 - 30 | 31 - 60 | 61 - 90 | 91 - 120 | 121 - 150 | 151 + | Total AR |
AA | $0 | $55 | $0 | $285 | $0 | $165 | $0 | $505 |
My report (data for first period looks correctly, second multiplied by 2 , third multiplied by 2 , fourth multiplied by 3 ) :
ACCTNUM | Patient | Sum ([Open Receivables]) |
---|---|---|
1120 | ||
AA | 23955 - SHARMA, B | 55 |
AA | 20514 - MCCORMACK, M | 260 |
AA | 29102 - BARLTROP, G | 310 |
AA | 14627 - ABRAMOVITZ, M | 495 |
See part of script below:
ODBC CONNECT TO [MS Access Database;DBQ=C:\Users\Админ\Desktop\TestDevelop\Test\Data\QVPreSalesData.mdb];
Facts:
LOAD
MEDNO,
TX,
INVOICE,
ACCTNUM,
IF (IsNull(QUANTITY), 0, QUANTITY) As QUANTITY,
IF (IsNull(U_PRICE), 0, U_PRICE) As U_PRICE,
Date(ApplyMap('DateMap', DATE, Null())) as DATE
Where Year(DATE)>='1994';
SQL SELECT *
FROM Treat;
Billing:
Join (Facts)
LOAD *,
IF($(varMaxDate) - Num(B_DATE)=0, 'Current',
IF($(varMaxDate) - Num(B_DATE)<=30, '1-30',
IF($(varMaxDate) - Num(B_DATE)<=60, '31-60',
IF($(varMaxDate) - Num(B_DATE)<=90, '61-90',
IF($(varMaxDate) - Num(B_DATE)<=120, '91-120',
IF($(varMaxDate) - Num(B_DATE)<=150, '121-150',
IF($(varMaxDate) - Num(B_DATE)>150, '151+'))))))) AS Receivebles_Age;
LOAD
INVOICE,
If(BILL > AMTPAID,
BILL - AMTPAID,0) as [Open Receivables],
Date(ApplyMap('DateMap', DATE, Null())) as B_DATE
;
SQL SELECT *
FROM txBilling;
LOAD *,
MEDNO&' '&'-'&' '&LNAME&','&' '&FNAME AS Patient;
LOAD
MEDNO,
LNAME,
If(IsNum(FNAME) OR FNAME=' ', 'N.A.', FNAME) AS FNAME;
SQL SELECT *
FROM Patients;
You probably have duplicate records as a result of the join. Perhaps an invoice has multiple records in the txBilling table.
Please post a small qlikview document that demonstrates the problem. Don't forget to scramble confidential information. See this document for more information: Preparing examples for Upload - Reduction and Data Scrambling