Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

lucasdavis500
Contributor II

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

Tags (3)
1 Reply
Highlighted
lucasdavis500
Contributor II

Re: If statement always evaluating first condition as true

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;