Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Specialist
Specialist

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;

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
1 Reply
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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