Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have been experiencing a problem with calculation of average turnaround times for a little while now & I must admit, im starting to run out of ideas !.
The basic problem is that I am trying to replicate results which are currently obtained via an SQL statement, which I don't seem to be able to put straight into QV. To make matters worse, we are working with a paradox database - so I cant easily see a 'top down' view of what is where, as is the case with SQL.
In any case, the following is the SQL that is being used currently:
SELECT S.Client,
R.Test,
S.Type,
MONTHNAME(recdate) AS Received_Month,
Sum (D.KPIREPEAT) As Repeats,
Sum (D.KPITEST) As Tests,
AVG(R.Approved-S.sampdate) As TotalTime,
AVG(R.Approved-S.Recdate) As TotalTime_FromReceipt,
AVG(S.logdate-S.recdate) As Total_Time_In_Prep,
AVG(R.Detdate-S.logdate) As TotalTime_InLab,
AVG(R.Approved-R.Detdate) As Time_Lab_to_Approval
FROM Samples S JOIN Results R on S.sampnum=R.sampnum Join Dets D on R.det=D.det
Where S.RECDATE>=?1 AND S.RECDATE<=?2 AND S.Sampstatus='APPROVED'
AND (D.KPITEST=1 OR D.KPIREPEAT=1)
Group By 1,2,3,4
UNION ALL
SELECT S.Client,
R.Test,
S.Type,
MONTHNAME(recdate) AS Received_Month,
Sum (D.KPIREPEAT) As Repeats,
Count (Distinct S.Sampnum) As Tests,
AVG(R.Approved-S.sampdate) As TotalTime,
AVG(R.Approved-S.Recdate) As TotalTime_FromReceipt,
AVG(S.logdate-S.recdate) As Total_Time_In_Prep,
AVG(R.Detdate-S.logdate) As TotalTime_InLab,
AVG(R.Approved-R.Detdate) As Time_Lab_to_Approval
FROM Samples S JOIN Results R on S.sampnum=R.sampnum Join Dets D on R.det=D.det
Where S.RECDATE>=?1 AND S.RECDATE<=?2 AND S.Sampstatus='APPROVED'
AND (R.Test='SIZING' OR R.Test='ICPTM')
Group By 1,2,3,4
With regards QV, I have pulled in each table separately (i.e: Tests,Results,Samples & Dets) & made sure they are linked correctly. Then, I have a straight table that im using, with received month (RECMONTH) as the dimension & AVG(approved-sampdate) as an example, for total time.
I cant see where im going wrong, but the results in QV are totally different to the current query. I think this is most likely down to my inexperience with complex sql. If anyone can help or point me in the right direction, it would be most appreciated
I suggest that you post the script you have already tried.
QV variable, table and field names are case sensitive, so RecDate, recdate and RECDATE are seen as separate fields. Could this be part of your problem?
How are you handling the parameterised part of the query (the ?1 and ?2 date values)?
Hi Jonathan, here is my full load script - with the parameterised part of the query, im just selecting the required received date
Results:
LOAD
*,
APPROVED-DETDATE as TimeLab_ToApproval;
LOAD
APPROVED,
Date (APPROVED) as APPROVED_DATE,
Date(DETDATE) as DETDATE,
DET,
SAMPNUM,
FROM
[..\..\..\QVData TEST\Energy Services\Data\Results.QVD]
(qvd)
WHERE Date(DETDATE)>'14/01/2015';
Samples:
LOAD
Date(APPDATE) as APPDATE,
Lookup('APPROVED',SAMPNUM,SAMPNUM,'Results') as Approved,
Lookup('DETDATE',SAMPNUM,SAMPNUM,'Results') as DetDate,
CLIENT,
CLIENTSREF,
LAB_TO,
date(LOGDATE) as LOG_DATE,
LOGDATE as LOGDATE,
RECDATE,
date (RECDATE) as REC_DATE,
Month(RECDATE) as RECMONTH,
year(RECDATE) as RECYR ,
SAMPDATE,
date (SAMPDATE) as SAMP_DATE,
month (SAMPDATE) as SAMPMONTH,
SAMPNUM,
SAMPSTATUS,
TURNAROUND,
TURNDAYS,
TYPE as SAMPLETYPE,
WEEKNUM
FROM
[..\..\..\QVData TEST\Energy Services\Data\Samples.QVD]
(qvd)
where SAMPSTATUS='APPROVED' and Date(RECDATE)>'14/01/2015' ;
Averages:
LOAD
//*,
SAMPLETYPE,
Month(RECDATE) as Recieved_Month,
CLIENT as Client_Averages,
APPDATE-SAMPDATE As TotalTime,
Approved-RECDATE as TotalTime_FromReciept,
LOGDATE-RECDATE as TotalTime_InPrep,
DetDate-LOGDATE as TotalTime_InLab
Resident Samples;
Store Averages into \\uk01-apv016\C$\Qlikview Test\QVData TEST\Energy Services\Data\Averages.QVD(qvd);
Dets:
LOAD
CATEGORY,
DET,
DETNAME,
DETORDER,
DETSTATUS ,
EXCLUDE,
KPITEST,
KPIREPEAT,
RPTGROUP,
TESTDATE
FROM
[..\..\..\QVData TEST\Energy Services\Data\Dets.QVD]
(qvd);
Tests:
LOAD
ANALYSTS,
TEST,
TEST_NAME,
TEST_REF,
TESTSTATUS
FROM
[..\..\..\QVData TEST\Energy Services\Data\Tests.QVD]
(qvd);
This is more different to your original post than it is similar - different fields, another table, different conditions and QVD rather than SQL sourced. So I am not sure how to answer your question which was to replicate the SQL (union and join) in QV.
Hi Jonathan,
There are more fields etc, but the same principal still applies - I just need to replicate the SQL statements in QV, because I cant simply copy & paste into the script
Have you tried to use Avg with Aggr in a straight table? Because SQL computes the average based on the attributes that are pulled into the query.
So maybe try like this: Aggr(Avg(your facts), Client, Test, Type, Monthname).
Hope this helps