Duplicate data issue

[vchuprina]

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

[swuehl]

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.

[avinashelite]

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 

[perumal_41]

Joining creating duplicate value.

Temporally use below expression in front end

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

[swuehl]

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?

Like

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.