Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

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;