Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vchuprina
New Contributor II

Duplicate data issue

Hi guys,

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

Source

MEDNOACCTNUMINVOICEBILLDATEAMTPAID
14627AA5555216511/6/19960
20514AA568971301/2/19970
29102AA569701551/6/19970
23955AA9700500553/18/19970

In file that I use for validation it also equal 505

Validation:          

AccountCurrent1 - 3031 - 6061 - 9091 - 120121 - 150151 +Total AR
AA$0$55$0$285$0$165$0$505

But in my application it for some reason equeal 1120

Application:  

ACCTNUMPatientSum ([Open Receivables])
Total 1120
AA23955 - SHARMA,  B55
AA20514 - MCCORMACK,  M260
AA29102 - BARLTROP,  G310
AA14627 - ABRAMOVITZ,  M495

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;

4 Replies
MVP
MVP

Re: Duplicate data issue

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

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
Valued Contributor II

Re: Duplicate data issue

Joining creating duplicate value.

Temporally use below expression in front end

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

MVP
MVP

Re: Duplicate data issue

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.

Community Browser