Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
lucasdavis500
Creator III
Creator III

If statement always evaluating first condition as true

Hey all, i'm new to qlikview, trying to develop a dashboard. I have run into the problem where my nested IF statements are always evaluating to TRUE in the first argument. The argument(s) involved BETWEEN with NUMERIC values. Initially, I had text with numeric, because I want my output, or "buckets", to have some text in them. However, I decided to change these to generic codes until my last step, then I'll insert the strings into my last IF function.

here's a sample of the code:

SecondCET:

LOAD *,

IF(ResolutionDate='Null',IF(MaxDateCreated-DateCreated>30, '1000', '1001'),ResolutionDate-DateCreated) AS ResponseTime

RESIDENT CET;

DROP TABLE CET;

ThirdCET:

LOAD *,

NUM(ResponseTime,'#,##0') AS [Response Time]

RESIDENT SecondCET;

DROP TABLE SecondCET;

FinalCET:

LOAD *,

IF([Response Time]>=0 OR [Response Time] < 7 , '0-7',

                                                IF([Response Time]>7 OR [Response Time] <= 14 , '8-14' ,

IF([Response Time] > 14 OR [Response Time] <=30, '15-30',

                                                IF([Response Time]>30, '>30',

IF([Response Time] = 1000, 'Outstanding >30',

                                                IF([Response Time]=1001, 'Outstanding <30')))))) AS ResponseBucket

RESIDENT ThirdCET;                                               

DROP TABLE ThirdCET;     

In the second step you can see that I've (attempted) to convert the ResponseTime to a Numeric, then process it in the next load statement with my nested IF Functions. I want these conditions to be BETWEEN and I think using OR should accomplish that, or maybe that's where my mistake is...                                    

1 Reply
lucasdavis500
Creator III
Creator III
Author

Changing the OR condition to AND (not sure why I had OR, slow morning) as well as re-arranging the conditions with strings helped. Qlikview must work like excel and evaluate strings as greater than any number i.e. 999,999,999. Here's what I change:

SecondCET:

LOAD *,

IF(ResolutionDate='Null',IF(MaxDateCreated-DateCreated>30, '1000', '1001'),ResolutionDate-DateCreated) AS ResponseTime

RESIDENT CET;

DROP TABLE CET;

ThirdCET:

LOAD *,

NUM(ResponseTime,'#,##0') AS [Response Time]

RESIDENT SecondCET;

DROP TABLE SecondCET;

FinalCET:

LOAD *,

IF([Response Time]>=0 AND [Response Time] < 7 , '0-7',

                                                IF([Response Time]>7 AND [Response Time] <= 14 , '8-14' ,

IF([Response Time] > 14 AND [Response Time] <=30, '15-30',

                                               IF([Response Time] = 1000, 'Outstanding >30',

IF([Response Time]=1001, 'Outstanding <30',

                                                IF([Response Time]>30, '>30',)))))) AS ResponseBucket

RESIDENT ThirdCET;                                              

DROP TABLE ThirdCET;