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]

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??

[Anonymous]

Nope that ends up making everything Night.

[Anonymous]

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.

[Anil_Babu_Samineni]

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

[Anonymous]

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.

[#7]

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

[Anonymous]

Not working, everything comes up as NIGHT.

[malini_qlikview · Accepted solution]

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

[#10]

can you upload sample qvw file?