topic If statement -> Loop in QlikView

If statement -> Loop

markgraham123 — Mon, 13 Jun 2016 12:30:29 GMT

Hi all,

I'm using If statement to filter out the data into buckets.

I was wondering whether the same can be achieved by Loop concept.

Any help is highly appreciated.

LOAD *,

If(

([Fiscal Week]>=1 and [Fiscal Week]<=4), 1,

If(

([Fiscal Week]>=5 and [Fiscal Week]<=8), 2,

If(

([Fiscal Week]>=9 and [Fiscal Week]<=12), 3,

If(

([Fiscal Week]>=13 and [Fiscal Week]<=16), 4,

If(

([Fiscal Week]>=17 and [Fiscal Week]<=20), 5,

If(

([Fiscal Week]>=21 and [Fiscal Week]<=24), 6,

If(

([Fiscal Week]>=25 and [Fiscal Week]<=28), 7,

If(

([Fiscal Week]>=29 and [Fiscal Week]<=32), 8,

If(

([Fiscal Week]>=33 and [Fiscal Week]<=36), 9,

If(

([Fiscal Week]>=37 and [Fiscal Week]<=40), 10,

If(

([Fiscal Week]>=41 and [Fiscal Week]<=44), 11,

If(

([Fiscal Week]>=48 and [Fiscal Week]<=52), 12)))))))))))) as [Fiscal Month];

Re: If statement -> Loop

sunny_talwar — Mon, 13 Jun 2016 12:33:06 GMT

What exactly do you mean when you say loop concept?

Re: If statement -> Loop

richard_chilvers — Mon, 13 Jun 2016 12:34:33 GMT

What happens to weeks 45 - 47 ?

Or perhaps they can't exist?

Re: If statement -> Loop

markgraham123 — Mon, 13 Jun 2016 12:41:41 GMT

Hi Richard,

Sorry for the confusion.

Let me re post the question with application atatched.

Thanks for your response.

Re: If statement -> Loop

markgraham123 — Mon, 13 Jun 2016 12:41:56 GMT

Sunny,

Sorry for the confusion.

Let me re post the question with application atatched.

Thanks for your response

Re: If statement -> Loop

marcus_sommer — Mon, 13 Jun 2016 14:41:04 GMT

Instead of using such a nested if-loop could the use of class(), pick(match()) or a mapping be better alternatives. Whereby I would suggest to create the fiscal month within a fiscal-calendar:

Fiscal Year

Fiscal Calendar with Non-Standard Days (Not 1-31)

- Marcus

Re: If statement -> Loop

richard_chilvers — Mon, 13 Jun 2016 14:48:18 GMT

Its worth noting that the nested IF is processed in sequence, so it could be tidied up as follows and becomes a bit more obvious to anyone trying to understand the script:

LOAD *,

If( [Fiscal Week]<=4), 1,

If( [Fiscal Week]<=8), 2,

If( [Fiscal Week]<=12), 3,

If( [Fiscal Week]<=16), 4, etc...........

However, my question above related to why there is a gap in the sequence.