Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
qlikmeplease
Contributor III

Nested IF in Qlikview

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,

1 Solution

Accepted Solutions
Highlighted
malini_qlikview
Contributor II

Re: Nested IF in Qlikview

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

View solution in original post

14 Replies
Highlighted
laddu_927
Valued Contributor

Re: Nested IF in Qlikview

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

Highlighted
qlikmeplease
Contributor III

Re: Nested IF in Qlikview

Nope that ends up making everything Night.

Highlighted
qlikmeplease
Contributor III

Re: Nested IF in Qlikview

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.

Highlighted

Re: Nested IF in Qlikview

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
qlikmeplease
Contributor III

Re: Nested IF in Qlikview

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.

Highlighted
Not applicable

Re: Nested IF in Qlikview

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

Highlighted
qlikmeplease
Contributor III

Re: Nested IF in Qlikview

Not working, everything comes up as NIGHT.

Highlighted
malini_qlikview
Contributor II

Re: Nested IF in Qlikview

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

View solution in original post

Highlighted
Not applicable

Re: Nested IF in Qlikview

can you upload sample qvw file?