Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmeplease
Creator III
Creator 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
malini_qlikview
Creator II
Creator II

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
laddu_927
Specialist
Specialist

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

qlikmeplease
Creator III
Creator III
Author

Nope that ends up making everything Night.

qlikmeplease
Creator III
Creator III
Author

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

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)
qlikmeplease
Creator III
Creator III
Author

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.

Not applicable

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

qlikmeplease
Creator III
Creator III
Author

Not working, everything comes up as NIGHT.

malini_qlikview
Creator II
Creator II

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

Not applicable

can you upload sample qvw file?