Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
no marco, I used pick/match. it worked perfectly for me. Thats exactly what i wanted.
ok, good that your issue seems solved.
Please close your thread:
Qlik Community Tip: Marking Replies as Correct or Helpful
thanks
regards
Marco