Nested IF in Qlikview

[Anonymous]

Hi,

I seem to be experiencing an issue related to my nested if.

IF(LGNUM='0320' AND MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri') AND TIME(CONFIRMED_AT,'HH:MM:SS')>='07:00:00' OR TIME(CONFIRMED_AT,'HH:MM:SS')<='16:00:00','DAY',IF(LGNUM='0320' AND MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun') AND TIME(CONFIRMED_AT,'HH:MM:SS')>='07:30:00' OR TIME(CONFIRMED_AT,'HH:MM:SS')<='14:00:00','DAY',

IF(LGNUM='0310' AND MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri') AND TIME(CONFIRMED_AT,'HH:MM:SS')>='08:00:00' OR TIME(CONFIRMED_AT,'HH:MM:SS')<='17:00:00','DAY',IF(LGNUM='0310' AND MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun') AND TIME(CONFIRMED_AT,'HH:MM:SS')>='08:00:00' OR TIME(CONFIRMED_AT,'HH:MM:SS')<='13:00:00','DAY','NIGHT')))) AS TEST

An Example, is that one of my times is 22:00:00 but it says its Day instead of Night.  Thus my statement seems to have an error.

Could an eagle eyed Expert help me out?

This is not even the full Nested if, i still have to broaden it , so if my logic is incorrect please let me know.

Just to explain, i want to identify Shifts for various places (LGNUM),however the shift start and end times fall into different intervals based on the weekday.  so the first few conditions (AND) all relate to the second part as well. 

Example:

so for all LGNUM='0320' that falls between Mon-Fri and TIME >=07:00:00 OR <16:00:00 Should be identified as day.

Thanks in advance.

Regards,

[Anonymous]

Please see attached, play on the front

face in the expression.

[Anonymous]

Thanks, this did work.  However i still need to incorporate the other statement aswell.  I will try make these changes to it to. 

Much appreciated.

[tunoi]

Try rewriting the IFS like below: it's possible that my syntax to have some error since i did it outside Qlikview.

IF(LGNUM='0320',

  if(     (MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri') AND (time(frac(CONFIRMED_AT,'HH:MM:SS'))>='07:00:00' OR time(frac(CONFIRMED_AT,'HH:MM:SS'))<='16:00:00'))

  or

  (MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun') AND (time(frac(CONFIRMED_AT,'HH:MM:SS'))>='07:30:00' OR time(frac(CONFIRMED_AT,'HH:MM:SS'))<='14:00:00')),

  'Day',

  'Night'),

  if( (MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri') AND (time(frac(CONFIRMED_AT,'HH:MM:SS'))>='08:00:00' OR time(frac(CONFIRMED_AT,'HH:MM:SS'))<='17:00:00'))

  or

  (MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun') AND (time(frac(CONFIRMED_AT,'HH:MM:SS'))>='08:00:00' OR time(frac(CONFIRMED_AT,'HH:MM:SS'))<='13:00:00'))

  'Day',

  'Night'),

)

[#5]

IF(LGNUM=0320 AND MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri')

AND (TIME(CONFIRMED_AT,'HH:MM:SS')>=Time#('07:00:00','HH:MM:SS')   AND TIME(CONFIRMED_AT,'HH:MM:SS')<=Time#('16:00:00','HH:MM:SS') ),'DAY',

IF(LGNUM=0320 AND MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun')

AND (TIME(CONFIRMED_AT,'HH:MM:SS')>=Time#('07:30:00','HH:MM:SS')  AND TIME(CONFIRMED_AT,'HH:MM:SS')<=Time#('14:00:00','HH:MM:SS')),'DAY',

IF(LGNUM='0310' AND MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri')

AND (TIME(CONFIRMED_AT,'HH:MM:SS')>=Time#('08:00:00','HH:MM:SS')AND TIME(CONFIRMED_AT,'HH:MM:SS')<=Time#('17:00:00','HH:MM:SS') ),'DAY',

IF(LGNUM='0310' AND MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun') AND (TIME(CONFIRMED_AT,'HH:MM:SS')>=Time#('08:00:00','HH:MM:SS')  AND TIME(CONFIRMED_AT,'HH:MM:SS')<=Time#('13:00:00','HH:MM:SS') ),'DAY','NIGHT'))))

[Anonymous]

Hi all,

took what I learnt from you guys and got the correct answer with a combination of the above, first and foremost the error was involved with the formatting of time.  However i corrected that using Frac as the Time# added the AM and PM which i didnt want, else the comparison would not work.

Furthermore, the syntax of my nested if was wrong, however i corrected it using this great tool.

http://www.qlikblog.at/tools/Nested-If-Generator.html

Thank you all for the replies, much appreciated.  Here below is my solution.

if(LGNUM='0320' AND  MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri') AND time(Frac(CONFIRMED_AT),'hh:mm:ss')>='07:00:00' and time(Frac(CONFIRMED_AT),'hh:mm:ss')<='16:00:00'

  ,'DAY'

  ,

  // Comment for Condition 2

  if(LGNUM='0320' AND  MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun') AND time(Frac(CONFIRMED_AT),'hh:mm:ss')>='07:30:00'and time(Frac(CONFIRMED_AT),'hh:mm:ss')<='14:00:00'

  ,'DAY'

  ,

  // Comment for Condition 3

  if(LGNUM='0310' AND  MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri') AND time(Frac(CONFIRMED_AT),'hh:mm:ss')>='08:00:00'and time(Frac(CONFIRMED_AT),'hh:mm:ss')<='17:00:00'

  ,'DAY'

  ,

  // Comment for Condition 4

  if(LGNUM='0310' AND  MATCH(WeekDay(CONFIRMED_AT),'Sat','Sun') AND time(Frac(CONFIRMED_AT),'hh:mm:ss')>='08:00:00'and time(Frac(CONFIRMED_AT),'hh:mm:ss')<='13:00:00'

  ,'DAY'

  ,'NIGHT'))))