Loop through months in every year.

[ganeshreddy]

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

[santiago_respane]

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]

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