topic Re: Nested IF in Qlikview in QlikView

Nested IF in Qlikview

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

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,

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 11:01:48 GMT

Shouldn't TIME(CONFIRMED_AT,'HH:MM:SS')>='07:00:00' OR TIME(CONFIRMED_AT,'HH:MM:SS')<='16:00:00','DAY'

have an "AND" in between??

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 11:08:13 GMT

Nope that ends up making everything Night.

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 11:21:45 GMT

I just did a test. I simplified the if statement and found that everything was incorrect when using the below.

IF(TIME(CONFIRMED_AT,'HH:MM:SS')>'04:00:00' AND TIME(CONFIRMED_AT,'HH:MM:SS')<='06:00:00','TRUE','FALSE')

Everything came up false. So there might be something else wrong.

Re: Nested IF in Qlikview

Anil_Babu_Samineni — Mon, 10 Apr 2017 11:32:58 GMT

How is your format looks like for CONFIRMED_AT. Can you try with Date function

IF(Date(CONFIRMED_AT,'hh:mm:ss')>'04:00:00' AND Date(CONFIRMED_AT,'hh:mm:ss')<='06:00:00','TRUE','FALSE')

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 11:53:10 GMT

Hi,

I solved the one part.

change of formats was needed first of all. that seemed to resolve the issue.

IF(LGNUM='0320' AND 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'),IF(LGNUM='0320' AND 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'))

However, adding the second if statement is failing.

Re: Nested IF in Qlikview

Mon, 10 Apr 2017 12:07:09 GMT

Hi,

Use below mentioned expression

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

AND (TIME(CONFIRMED_AT,'HH:MM:SS')>='07:00:00' AND 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' AND 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' AND 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' AND TIME(CONFIRMED_AT,'HH:MM:SS')<='13:00:00'),'DAY','NIGHT'))))

AS TEST

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 12:11:48 GMT

Not working, everything comes up as NIGHT.

Re: Nested IF in Qlikview

malini_qlikview — Mon, 10 Apr 2017 12:14:28 GMT

Hi,

Try this

=IF(LGNUM='0320' AND MATCH(WeekDay(CONFIRMED_AT),'Mon','Tue','Wed','Thu','Fri') AND Time#(Time(CONFIRMED_AT),'hh:mm:ss')>=Time#('07:00:00','hh:mm:ss') and Time#(Time(CONFIRMED_AT),'hh:mm:ss')<=Time#('16:00:00','hh:mm:ss'),'Day','Night')

Re: Nested IF in Qlikview

Mon, 10 Apr 2017 12:14:39 GMT

can you upload sample qvw file?

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 12:22:54 GMT

Please see attached, play on the front

face in the expression.

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 12:36:32 GMT

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.

Re: Nested IF in Qlikview

tunoi — Mon, 10 Apr 2017 12:43:11 GMT

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

(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'))

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

)

Re: Nested IF in Qlikview

Mon, 10 Apr 2017 12:45:07 GMT

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

Re: Nested IF in Qlikview

Anonymous — Mon, 10 Apr 2017 12:57:23 GMT

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