How to combine the following KPI Calculation into Single

[madhubabum]

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

[swuehl]

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;

[madhubabum]

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

[swuehl]

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?

[madhubabum]

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

[madhubabum]

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