Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Here i am calculating metrics by looping through months in every year (Ex : i got data from 1977 to 2018) , below code is not working . can anyone suggest how to increment year value correctly.
Note: i need to use minMonth and maxMonth due to where condition
Temp_month:
load num(min(Month(wo_completionDate))) as minMonth,
num(max(Month(wo_completionDate))) as maxMonth,
min(year(wo_completionDate)) as minYear,
max(year(wo_completionDate)) as maxYear
from $(vQVDDataDir)\ WO.qvd (qvd) ;
let vMinMonth= peek('minMonth');
let vMaxMonth= peek('maxMonth');
let vMinYear=peek('minYear');
let vMaxYear=peek('maxYear');
let i=$(vMinMonth);
let j=$(vMinYear);
do while i<=$(vMaxMonth) and j<=$(vMaxYear)
join(Summary)
load
'$(i)' as Month,
'$(j)' as Year,
fd_code as [Client source],
count(if(code=1 and wc_code = '01' and Diff <=1 , wo )) as CorrWoPriority1Num,
count(if(pricode=1 and wc_code = '01' ,wo_number)) as CorrWoPriority1Den,
count(if(code=2 and wc = '01' and Diff <=2,wo )) as CorrWoPriority2Num,
count(if(code=2 and wc = '01' ,wo_number)) as CorrWoPriority2Den,
count(if(code=3 and wc = '01' and Diff <=28,wo )) as CorrWoPriority3Num,
count(if(code=3 and wc = '01' ,wo_number)) as CorrWoPriority3Den,
count(if(ode=4 and wc = '01' and Diff <=14 ,wo )) as CorrWoPriority4Num,
count(if(code=4 and wc = '01' ,wo )) as CorrWoPriority4Den
from $(vQVDDataDir)\ WO.qvd (qvd)
where num(month(floor(wo_completionDate))) >= '$(i)'-1 and num(month(floor(wo_completionDate))) <= '$(i)'
and isnull(proj_number)
group By
fd_code;
Let i=i+1;
let j=j+1;
LOOP
STORE Summary INTO [$(vQVDDataDir) summary.qvd] (qvd);
Best Regards,
Ganesh
Hi Ganesh,
please explain more what is the problem you are trying to solve here, what is the requirement.
Also you should verify your code because it seems to be that some fields are named incorreclty, like this:
count(if(ode=4 and wc = '01' and Diff <=14 ,wo )) as CorrWoPriority4Num,
Kindest regards,
Santiago Respane
Hi Ganesh, weird way to do the loop, you can't add '1' to 'i' and 'j' each iteration:
if this starts with 01/1977 i=1, J=1977, next iteration will be i=2, J=1978
following this approach you can check when to add 1 to j:
Try Changing:
Let i=i+1;
let j=j+1;
To:
Let j=If(i=12, j+1, j);
Let i=If(i=12, 1, i+1);
Another thing, in the where you're using >= and <=, so each iteration will have 2 months loaded, maybe you need to remove the '=' in one of the conditions.
HI ,
Try some thing like below
Use FOR loop here, if Min month and max month are available you can use them or
else create them like
vMinMonth=1;
vmaxMonth=12;
FOR i=$(vMinYear)-1 to $(vMaxYear)-1
FOR j=$(vMinMonth) to $(vMaxMonth)
LOAD
$(J) as Month,
$(i)+1 as Year,
fd_code as [Client source],
.
.
.
.
NEXT
NEXT
btw, you also need to change the while conditions to:
do while j<$(vMaxYear) or (i<=$(vMaxMonth) and j=$(vMaxYear))