topic Problem with multiple If-Clause in QlikView

Problem with multiple If-Clause

Fri, 17 Jun 2011 09:31:23 GMT

Hey there,

I'm trying to integrate an If-Clause, which groups my days (1 till 31) into 2.

The reason is that there are transactions two times a month. Therefore it should be like this:

Day 1-4 and Day 20-31 = Transaction on Day 5

and

Day 5-19= Transaction on Day 20

Since I don't want to group them by typing each number manually, like:

If(Day=1, '5',

If(Day=2, '5',

If(Day=3, '5'...))) as GroupedDays

Cause it's simply to long-winded!

My idea was this:

If(Day>=5, If(Day<20, '20',

If(Day>=20,If(Day<5,'5',))))as TestDay

Synthax check is OK.

The problem I'm having here's that my new column just includes 20, not 5. Although the link towards 'day' is particulary working.

Means:

When I select 20 in 'TestDay' I get 5-19, which is correct.

But where's my 5 in 'TestDay', any clue?

Thanks

Problem with multiple If-Clause

pat_agen — Fri, 17 Jun 2011 10:01:49 GMT

hi,

load a mapping table and use applymap. Look in help and on forum for examples. (might be long-winded but you will learn for the future and save yourself time!)

You will have to write out 15 of the days followed by a comma followed by 20. But xl has many useful functionalities to help you do this like copy/paste. the other 16 days just let the default parameter in the applymap() function do that for you.

to get you on your way here is a link to Steve Dark's blog with a post about applymap.

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Problem with multiple If-Clause

erichshiino — Fri, 17 Jun 2011 10:28:46 GMT

I think you should go for the applymap as mentioned in the other post or use intervalmatch.

If you want to keep the if-statement, you can make it simple:

If(Day>=5 and Day<=19, 20, 5)

Hope this helps,

Erich

Problem with multiple If-Clause

Fri, 17 Jun 2011 10:45:39 GMT

thx