topic Re: Duplicate data issue in QlikView

Duplicate data issue

vchuprina — Sun, 03 Apr 2016 21:15:00 GMT

Hi guys,

I've problem with one load of my script. In source table total BILL for company AA is 505

Source

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 file that I use for validation it also equal 505

Validation:

Account	Current	1 - 30	31 - 60	61 - 90	91 - 120	121 - 150	151 +	Total AR
AA	$0	$55	$0	$285	$0	$165	$0	$505

But in my application it for some reason equeal 1120

Application:

ACCTNUM	Patient	Sum ([Open Receivables])
Total		1120
AA	23955 - SHARMA, B	55
AA	20514 - MCCORMACK, M	260
AA	29102 - BARLTROP, G	310
AA	14627 - ABRAMOVITZ, M	495

Can you please look at my script and find issue:

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: Duplicate data issue

swuehl — Mon, 04 Apr 2016 09:32:14 GMT

It's most likely that your JOIN duplicated records.

It's hard to tell in detail without knowing the input records of your SQL tables.

Or maybe you linked an invoice multiple times to patients. Your first tables seems not to be just doubled in amount, there seems to be some amount shifting also.

Could you post your document log?

And, also important, on which version are you? There are issues with QV12SR1, so if you are on SR1, be warned that there are issues with JOIN.

Re: Duplicate data issue

avinashelite — Mon, 04 Apr 2016 09:51:19 GMT

The issue is with JOIN s and multiple loading statements....Try load the tables independently and allow the Qlikview to form the association ..if everything is ok then try to play with your conditional and join statements

Re: Duplicate data issue

perumal_41 — Mon, 04 Apr 2016 10:06:17 GMT

Joining creating duplicate value.

Temporally use below expression in front end

sum (aggr (Distinct Sum(Distinct [Open Receivables]),Patient))

Re: Duplicate data issue

swuehl — Mon, 04 Apr 2016 10:40:27 GMT

Besides all said about JOINs, aren't you comparing two different values?

In your first table, you show BILL values (aggregated?) per MEDNO.

In your last table, you are showing Open Receivables, which are derived from BILL using:

LOAD

INVOICE,

If(BILL > AMTPAID,

BILL - AMTPAID,0) as [Open Receivables],

Date(ApplyMap('DateMap', DATE, Null())) as B_DATE

;

SQL SELECT *

FROM txBilling;

I also assume there are multiple records / transactions for a bill and you essentially want an aggregate here?

LOAD

INVOICE,

If(Only(BILL) > Sum(AMTPAID),

Only(BILL) - Sum(AMTPAID),0) as [Open Receivables],

Date(ApplyMap('DateMap', Max(DATE), Null())) as B_DATE

GROUP BY INVOICE

;

SQL SELECT *

FROM txBilling;

Not sure about the aggregation functions we need to use in the LOAD, that's depending on your requirements.