Discussion Board for collaboration on QlikView Scripting.
I've problem with one load of my script. In source table total BILL for company AA is 505
In file that I use for validation it also equal 505
But in my application it for some reason equeal 1120
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];
IF (IsNull(QUANTITY), 0, QUANTITY) As QUANTITY,
IF (IsNull(U_PRICE), 0, U_PRICE) As U_PRICE,
Date(ApplyMap('DateMap', DATE, Null())) as DATE
SQL SELECT *
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;
If(BILL > AMTPAID,
BILL - AMTPAID,0) as [Open Receivables],
Date(ApplyMap('DateMap', DATE, Null())) as B_DATE
MEDNO&' '&'-'&' '&LNAME&','&' '&FNAME AS Patient;
If(IsNum(FNAME) OR FNAME=' ', 'N.A.', FNAME) AS FNAME;
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.
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
Joining creating duplicate value.
Temporally use below expression in front end
sum (aggr (Distinct Sum(Distinct [Open Receivables]),Patient))
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:
If(BILL > AMTPAID,
I also assume there are multiple records / transactions for a bill and you essentially want an aggregate here?
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
Not sure about the aggregation functions we need to use in the LOAD, that's depending on your requirements.