topic Re: nested if statement in QlikView

nested if statement

jadams123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys:

I have 100+ nested if statements...I know there is a limit to if statements

What are my alternatives to this this formula:

if(YEAR = '2017' and DAY >= 2415 and DAY <= 2421 ,'WK1',

if(YEAR = '2017' and DAY >= 3183 and DAY <= 3189 ,'WK1',

if(YEAR = '2017' and DAY >= 2422 and DAY <= 2428 ,'WK2',

if(YEAR = '2017' and DAY >= 3190 and DAY <= 3196 ,'WK2',

and so on...

Thanks,

Re: nested if statement

MarcoWedel — Wed, 02 Nov 2016 23:14:44 GMT

Can you describe your data and required result in more detail (especially your DAY field)?

A substitute for these nested if() functions might be a Pick(Match()) combination, but there should be an easier solution.

thanks

regards

Marco

Re: nested if statement

jadams123 — Wed, 02 Nov 2016 23:24:56 GMT

yes basicall i am making a calculated dimension: Quarter, Period, Week, Day.

I am making a calculated dim because IT dept has told me they have other projects pending.

How would i be able to use pick match based on above if statement...

Re: nested if statement

Anonymous — Thu, 03 Nov 2016 00:29:49 GMT

I tend to use mapping tables for situations like this. You could build it in excel or use an inline load. Essentially what you would do is load up all your dates in one column and the week values in another, then use ApplyMap to create the new column in your main expression. For example

Week_Map:

Mapping LOAD * INLINE [

Day, Week

2415, WK1

2416, WK1

2417, WK1

2418, WK1

2419, WK1

2420, WK1

2421, WK1

etc...

];

Then simply call

ApplyMap('Week_Map', DAY, 'Missing') AS Week

given how many dates you're checking it would probably be much neater to load the mapping table from a spreadsheet.

Re: nested if statement

johnw — Thu, 03 Nov 2016 00:37:15 GMT

Well, I don't know the pattern for other years, and I can barely make sense out of the pattern for 2017. But this produces the same results for data that's in range of your numbers, and follows A pattern beyond them. Just not sure it's the right pattern.

'WK' & if(DAY < 3183,floor((DAY-2408)/7),floor((DAY-3176)/7))

In any case, the basic idea is "use math".

Re: nested if statement

jadams123 — Thu, 03 Nov 2016 02:04:29 GMT

hmm...i want to avoid dividing by 7...

So i have a range of data and i want to get them in Wk's

"DAY" basically means a Day number...so 2415 means monday or so

how would i simply write this code without violating the limitations i have with if statement.

I just want to write:

1) Pick year 2017

2) select a range "DAY >= 2415 and DAY <= 2421"

3) = "WK1"

thanks,

Re: nested if statement

deepakqlikview_123 — Thu, 03 Nov 2016 03:42:00 GMT

It would be better if you can use applymap function in this case.

Thanks

Re: nested if statement

jadams123 — Thu, 03 Nov 2016 10:00:33 GMT

i dont have developer access. i think the only choice i have is expressions

Re: nested if statement

Anonymous — Thu, 03 Nov 2016 11:36:17 GMT

Hi,

Couldn't you handle this in your calendar? A backend script would look something like:

(WeekStart(<YourDate>) - <FirstMondayOfYear>) / 7 + If(tmpWDYear = 0, 1, 2) As YearWeekNum,

((WeekStart(<YourDate>) - <FirstMondayOfMonth>) / 7 + 2) As MonthWeek,

<YourDateYear>& ' ' & Quarter as YearQuarter

And then you can just use the field in the front-end

Re: nested if statement

johnw — Thu, 03 Nov 2016 15:55:52 GMT

If 2415 is the Monday of the first week of 2017, then, uh... is May 25, 2010 day 1? Except that then 3183 would be February 9, 2019, and a Saturday. I'm sorry, but "a day number" doesn't give me any clue what those numbers actually mean.

And why do you want to avoid dividing by 7? Does it not work for some reason? What reason?

Re: nested if statement

MarcoWedel — Thu, 03 Nov 2016 22:48:23 GMT

Hi,

one solution using pick(match()) could be:

LOAD *,
    Pick(Match(-1,
            YEAR = '2017' and DAY >= 2415 and DAY <= 2421,
            YEAR = '2017' and DAY >= 3183 and DAY <= 3189,
            YEAR = '2017' and DAY >= 2422 and DAY <= 2428,
            YEAR = '2017' and DAY >= 3190 and DAY <= 3196),
          'WK1',
          'WK1',
          'WK2',
          'WK2') as WEEK       
Inline [
YEAR, DAY
2017, 2415
2017, 2416
2017, 2417
2017, 2418
2017, 2419
2017, 2420
2017, 2421
2017, 2422
2017, 2423
2017, 2424
2017, 2425
2017, 2426
2017, 2427
2017, 2428
2017, 3183
2017, 3184
2017, 3185
2017, 3186
2017, 3187
2017, 3188
2017, 3189
2017, 3190
2017, 3191
2017, 3192
2017, 3193
2017, 3194
2017, 3195
2017, 3196
];

hope this helps

regards

Marco

Re: nested if statement

jadams123 — Thu, 03 Nov 2016 22:57:21 GMT

john- i was just giving an example for 2415..If i am not mistaken 2415 was 08/01/2016. Which was actually a friday for one location. And 08/01/2016 was for location 2. And their Day number starts from 3183.

I did actually learned to do pick match today...took me a while.

by pick(match(Day, '2415', '2416'...), 'WK1', 'WK1'...)

I will try this in expressions as i don't have developer access...Hopefully it works

Pick(Match(-1,

YEAR = '2017' and DAY >= 2415 and DAY <= 2421,

YEAR = '2017' and DAY >= 3183 and DAY <= 3189,

YEAR = '2017' and DAY >= 2422 and DAY <= 2428,

YEAR = '2017' and DAY >= 3190 and DAY <= 3196),

'WK1',

'WK2',

'WK2')

Thanks,

Re: nested if statement

MarcoWedel — Thu, 03 Nov 2016 23:11:12 GMT

but like already said, if you could provide some information about the DAY number definition, we might able to provide a much more efficient solution like:

'WK'&Div(Mod(DAY,768)-104,7) as WEEK

(most likely this expression only works for your specific sample data)

LOAD *,
     'WK'&Div(Mod(DAY,768)-104,7) as WEEK;
LOAD 2017 as YEAR,
     2414+RecNo()-733*(RecNo()>35) as DAY    //only generating some sample data
AutoGenerate 70;

hope this helps

regards

Marco

Re: nested if statement

MarcoWedel — Thu, 03 Nov 2016 23:34:04 GMT

or for the whole year?!?!

LOAD *,
    'WK'&Div(Mod(DAY,768)-104,7) as WEEK;
LOAD 2017 as YEAR,
    2414+RecNo()-403*(RecNo()>365) as DAY   //again only generating some sample data
AutoGenerate 730;

regards

Marco

Re: nested if statement

MarcoWedel — Sat, 05 Nov 2016 00:34:01 GMT

Nice that Pick(Match()) worked for you, but I guess you don't want to use it.

Instead what you really want is a table of start dates for each of your different locations and only one small expression to calculate your weeks with.

Maybe you could mapping load this location/startdate table and use ApplyMap()‌ in your expression to translate the location into a startdate.

If you can provide some more location/startdate combinations and your week definition then maybe we can help with the expression...

hope this helps

regards

Marco

Re: nested if statement

jadams123 — Tue, 08 Nov 2016 13:05:05 GMT

no marco, I used pick/match. it worked perfectly for me. Thats exactly what i wanted.

Re: nested if statement

MarcoWedel — Tue, 08 Nov 2016 19:54:30 GMT

ok, good that your issue seems solved.

Please close your thread:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco