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

Duplicated data issue

Hi Guys,

I've a problem with one table, data all time duplicated. For example company AA have following data:

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

In report I should have following (Sum equal 505):

         

AccountCurrent1 - 3031 - 6061 - 9091 - 120121 - 150151 +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 ) :  

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

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;

1 Reply

Re: Duplicated data issue

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


talk is cheap, supply exceeds demand
Community Browser