Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jadams123
Creator
Creator

nested if statement


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,

J

16 Replies
MarcoWedel

Hi,

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

QlikCommunity_Thread_238497_Pic1.JPG

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

jadams123
Creator
Creator
Author

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',

          'WK1',

          'WK2',

          'WK2')

Thanks,

J

MarcoWedel

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)

QlikCommunity_Thread_238497_Pic2.JPG

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

MarcoWedel

or for the whole year?!?!

QlikCommunity_Thread_238497_Pic3.JPG

QlikCommunity_Thread_238497_Pic4.JPG

QlikCommunity_Thread_238497_Pic5.JPG

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

MarcoWedel

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

jadams123
Creator
Creator
Author

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

MarcoWedel

ok, good that your issue seems solved.

Please close your thread:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco