Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
VC1258
Contributor III
Contributor III

Expression logic help

Any help with this is appreciated. I am trying to label any records on Friday, Saturday and Sunday all day and Monday before 12:01PM "On time" and anything older than that "Late" or later than Monday 12:01PM "late". This is the logic I've used so far, but I still can't seem to find a way to get it to work 100%
 
        IF(TEST_RESULT = '30'
           AND Interval(Now() - RESULT_DATE,'D') >= 6, 'Late',
        IF(TEST_RESULT = '30'
           AND (num(weekday( [RESULT_DATE], 6 ))) = 1
           AND Time(RESULT_DATE, 'hh:mm:ss') < '12:01:00 PM'
           AND Interval(Now() - RESULT_DATE,'D') < 6, 'On time',  
        IF(TEST_RESULT = '30'
           AND (num(weekday( [RESULT_DATE], 6 ))) = 1
           AND Time(RESULT_DATE, 'hh:mm:ss') > '12:01:00 PM'
           AND Interval(Now() - RESULT_DATE,'D') < 4, 'Late',
        IF(TEST_RESULT = '30'
           AND (num(weekday( [RESULT_DATE], 6 ))) = 2
           OR  (num(weekday( [RESULT_DATE], 6 ))) = 3
           OR  (num(weekday( [RESULT_DATE], 6 ))) = 4
           AND Interval(Now() - RESULT_DATE,'D') <= 6, 'Late',
        IF(TEST_RESULT = '30'
           AND (num(weekday( [RESULT_DATE], 6 ))) = 5
           OR  (num(weekday( [RESULT_DATE], 6 ))) = 6
           OR  (num(weekday( [RESULT_DATE], 6 ))) = 0
           AND Interval(Now() - RESULT_DATE,'D') < 5, 'On time',
        ))))) AS [Status],
 
 
Attached picture has the columns used ([RESULT_DATE], [STATUS],[DAYS SINCE LAST TEST])
Labels (2)
1 Reply
marcus_sommer

I think the AND and OR within the last two if-loops aren't correct defined - they need be appropriate wrapped with brackets to ensure the right order of execution.

Beside this an extra wrapping of a function doesn't improved the readability and the if-query to TEST_RESULT is redundant and could shortened. Further such a construct could be simplified by applying a multi-stage approach within a preceeding-load, means something like:

load *, if(match(X, 5, 6), 'x', 'y') as Y;
load *, num(weekday(RESULT_DATE)) as X
from Y;

- Marcus