Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
ganeshreddy
Creator III
Creator III

Loop through months in every year.

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

4 Replies
santiago_respane
Specialist
Specialist

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

rubenmarin

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.

sasikanth
Master
Master

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

rubenmarin

btw, you also need to change the while conditions to:

do while j<$(vMaxYear) or (i<=$(vMaxMonth) and j=$(vMaxYear))