Duplicated data issue

vchuprina — Sun, 03 Apr 2016 16:15:54 GMT

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
14627	AA	55552	165	11/6/1996
20514	AA	56897	130	1/2/1997
29102	AA	56970	155	1/6/1997
23955	AA	9700500	55	3/18/1997

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;

Re: Duplicated data issue

Gysbert_Wassenaar — Mon, 04 Apr 2016 08:45:14 GMT

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

topic Duplicated data issue in QlikView

Duplicated data issue

Re: Duplicated data issue