topic Re: Loop through months in every year. in QlikView

Loop through months in every year.

ganeshreddy — Mon, 02 Nov 2015 11:39:55 GMT

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

Re: Loop through months in every year.

santiago_respane — Mon, 02 Nov 2015 12:24:09 GMT

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

Re: Loop through months in every year.

rubenmarin — Mon, 02 Nov 2015 12:39:33 GMT

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.

Re: Loop through months in every year.

sasikanth — Mon, 02 Nov 2015 13:09:57 GMT

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

Re: Loop through months in every year.

rubenmarin — Mon, 02 Nov 2015 13:41:23 GMT

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

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