topic Re: How to combine the following KPI Calculation into Single in QlikView

How to combine the following KPI Calculation into Single

madhubabum — Thu, 29 Oct 2015 07:42:05 GMT

Hi Experts

How to combine multiple blocks of code into one iteration of the loop. For e.g. lines " KPI 2 " can be included along with the block of code from lines " KPI 1 "

Summary:

Load floor(wo_completionDate)-floor(wo_requestDate) as wo_Diff,

fd_code as [Client source],

wo_number,

year(floor(wo_completionDate)) as Year,

num(month(floor(wo_completionDate)),'00') as Month,

pri_code,wc_code,proj_number

from $(vQVDDataDir)\ARMK_Facilities_Optimization_WO.qvd (qvd) where year(floor(wo_completionDate))='2015'

and num(month(floor(wo_completionDate)),'00') >='06' and num(month(floor(wo_completionDate)),'00')<='10';

//KPI 1
let vMinMonth= '06';
let vMaxMonth= '10';

for i= '06' to '10'

temp_Summary:
load
     '$(i)' as Month,
     [Client source],
     count(wo_number) as [No Of Work Orders 1]
     resident Summary where pri_code= 1 and wc_code = '01'
     and wo_Diff <=1
     and isnull(proj_number)
     and Month <= $(i) and Month >= $(i)-1
     group By
     [Client source]
     ;
next i

left join(Summary)
Load Month,[Client source], [No Of Work Orders 1] resident temp_Summary;
drop table temp_Summary;
next;

//KPI 2
let vMinMonth= '06';
let vMaxMonth= '10';

for i= '06' to '10'

temp_Summary:
LOAD
     '$(i)' as Month,
     [Client source],
     count(wo_number) as [No Of Work Orders 1.1]
resident Summary where pri_code= 1 and wc_code = '01'
     and isnull(proj_number)
    and Month <= $(i) and Month >= $(i)-1
     group by
     [Client source]
     ;
next i
left join(Summary)
Load Month,[Client source], [No Of Work Orders 1.1] resident temp_Summary;
drop table temp_Summary;

Thanks

Madhu

Re: How to combine the following KPI Calculation into Single

swuehl — Thu, 29 Oct 2015 08:00:00 GMT

Something like:

let vMinMonth= '06';
let vMaxMonth= '10';

for i= '06' to '10'

temp_Summary:
load
     '$(i)' as Month,
     [Client source],
     count(if(wo_Diff <=1 ,wo_number)) as [No Of Work Orders 1],
     count(wo_number) as [No Of Work Orders 1.1]
resident Summary where pri_code= 1 and wc_code = '01'
//     and wo_Diff <=1
     and isnull(proj_number)
     and Month <= $(i) and Month >= $(i)-1
     group By
     [Client source]
     ;
next i

left join(Summary)
Load Month,[Client source], [No Of Work Orders 1], [No Of Work Orders 1.1]   resident temp_Summary;
drop table temp_Summary;

Re: How to combine the following KPI Calculation into Single

madhubabum — Thu, 29 Oct 2015 08:11:45 GMT

HI Swuehl

Thanks for ur reply

KPI 1 : [No Of Work Orders 1],

KPI 2: [No Of Work Orders 1.1]

are showing as same values (No Difference)

How we can Calculate ?

Thanks

Re: How to combine the following KPI Calculation into Single

swuehl — Thu, 29 Oct 2015 08:26:09 GMT

If wo_Diff value for the records that pass where clause is <=1, then both KPI will be the same.

Your previous script showed a different result?

Re: How to combine the following KPI Calculation into Single

madhubabum — Thu, 29 Oct 2015 09:46:39 GMT

Hi Swuehl

Thanks for your valuable suggestion. It working

With the help of your Suggestible code , We are implemented the following code

Summary:

Load
fd_code as [Client source],
year(floor(wo_completionDate)) as Year,
num(month(floor(wo_completionDate))) as Month

from $(vQVDDataDir)\ Optimization_WO.qvd (qvd) where year(floor(wo_completionDate))='2015' ;;

Temp_month:
load num(min(month(wo_completionDate))) as minMonth,
num(max(month(wo_completionDate))) as maxMonth
from $(vQVDDataDir)\ Optimization_WO.qvd (qvd) where year(floor(wo_completionDate))='2015' ;

let vMinMonth= peek(minMonth);
let vMaxMonth= peek(maxMonth);

do while i=$(vMinMonth)<$(vMaxMonth)
left join(Summary)
load
Month,
Year,
[Client source],
if(pri_code= 1 and wc_code = '01' and wo_Diff <=1 and isnull(proj_number),count(wo_number)) as [No Of Work Orders 1],
if(pri_code= 1 and wc_code = '01' and isnull(proj_number),count(wo_number)) as [No Of Work Orders 1.1],
if(pri_code= 2 and wc_code = '01' and wo_Diff <=2 and isnull(proj_number),count(wo_number)) as [No Of Work Orders 2],
if(pri_code= 2 and wc_code = '01' and isnull(proj_number),count(wo_number)) as [No Of Work Orders 2.1],
if(pri_code= 3 and wc_code = '01' and wo_Diff <=28 and isnull(proj_number),count(wo_number)) as [No Of Work Orders 3],
if(pri_code= 3 and wc_code = '01' and isnull(proj_number),count(wo_number)) as [No Of Work Orders 3.1],
if(pri_code= 4 and wc_code = '01' and wo_Diff <=14 and isnull(proj_number),count(wo_number)) as [No Of Work Orders 4],
if(pri_code= 4 and wc_code = '01' and isnull(proj_number),count(wo_number)) as [No Of Work Orders 4.1]
from $(vQVDDataDir)\ Optimization_WO.qvd (qvd)
where Month >= $(i)-1 and Month <= $(i)
group By
[Client source]
;
Let i=i+1;
LOOP

// How to Implement the Following Piece of Code ....Any Suggestions ?

NOTE : my requirement is

"extract the count of records by year month and client and add to Qlikview table created in #2

for each KPI"

"Wo_completionDate between the beginning of prior month and the end of current month"

Thanks

Madhu

Re: How to combine the following KPI Calculation into Single

madhubabum — Thu, 29 Oct 2015 11:17:52 GMT

Hi Swuehl

Please suggest me ,

How we can calculate the Wo_completionDate between the beginning of prior month and the end of current month

Thanks