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.
Not applicable

Problem with Averages

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

6 Replies
MVP
MVP

Re: Problem with Averages

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Problem with Averages

How are you handling the parameterised part of the query (the ?1 and ?2 date values)?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Problem with Averages

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);

 

 

 

 

MVP
MVP

Re: Problem with Averages

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Problem with Averages

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

sinanozdemir
Valued Contributor III

Re: Problem with Averages

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

Community Browser