Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
lucasdavis500
Contributor II

Using IF condition where the criteria is NE

I have a fairly large condition I'm trying to create for a text object, but I cannot get my Set Analysis or IF(NOT) functions to work properly.

Basically, what I have done is created a variable called Response Time in my data. I had a column with strings/numbers, and needed all of the values to be numeric, for calculation purposes, so I created a code for the strings, I.E. '1000' and '1001. None of my values will ever reach these numbers, so it's safe to keep them as codes.

I'm trying to do create some text objects based on calculations done on ALL of the values EXCEPT for the codes I created.

I.E., at a simple level:

=SUM({<[Response Time] -= {'1000','1001'}>} [Response Time]) / COUNT({<[Response Time] -= {'1000','1001'}>} [Response Time])

I want '1000' and '1001' excluded. I've also tried:

=(if(Not wildmatch([Response Time], '1000', '1001'), SUM([Response Time])) / (COUNT(Pkey)))

which returns '-'

The first formula is resolving, but it seems they it is keeping the '1000' and '1001' codes in the calculations, because the numbers generated are >100, which I know is not true, and the second returns NULL... am I missing something in the usage of if(Not wildmatch)?

12 Replies

Re: Using IF condition where the criteria is NE

It seems like it has something to do with how you are calculating your Response Time and assigning them the value of 1000 and 1001. Can you share your script where you manipulate Response Time?

lucasdavis500
Contributor II

Re: Using IF condition where the criteria is NE

//In my LOAD Script:

CET:

ALT(DateResolved, 'Null') as ResolutionDate,

LEFT JOIN(CET)

LOAD MAX(DateCreated) AS MaxDateCreated

RESIDENT CET;

//After I'm loading:

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.00') AS [Response Time]

RESIDENT SecondCET;
DROP TABLE SecondCET;


The weird thing is, is that I actually manipulate [Response Time] in a different section of my script, like so:


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] <=21, '15-21',
IF([Response Time] > 21 AND [Response Time] <=30, '21-30',
IF([Response Time] = 1000, 'Outstanding >30',
IF([Response Time]=1001, 'Outstanding <30',
IF([Response Time]>30, '>30'))))))) AS ResponseBucket

When I use [Response Time] here, it seems to be working the way I want it to...

Partner
Partner

Re: Using IF condition where the criteria is NE

hi, this seems to work for me, see attached. i have excluded Periods 1 and 2 in my example which works.

Its probably something to do with the field you are manipulating

Re: Using IF condition where the criteria is NE

Can you check by adding floor here?

ThirdCET:
LOAD *,
NUM(Floor(ResponseTime),'#,##0.00') as [Response Time]

lucasdavis500
Contributor II

Re: Using IF condition where the criteria is NE

Hi Sunny, this seemed to change all of my [Response Time] values to '-'

Re: Using IF condition where the criteria is NE

How about this:

ThirdCET:

LOAD *,

          Num(Floor(Num#(ResponseTime)),'#,##0.00') as [Response Time]

lucasdavis500
Contributor II

Re: Using IF condition where the criteria is NE

This has returned some of my values. The values are formatted as 1.00, 2.00, 3.00, etc... with 2 decimal places, but the decimals are not populated.... Which they should be in some cases... My IF/SET analysis functions are still not calculating right...they seem to be keeping '1000' and '1001' in the calculation still...

lucasdavis500
Contributor II

Re: Using IF condition where the criteria is NE

what is the affect you're trying to achieve by adding FLOOR(NUM#(

Re: Using IF condition where the criteria is NE

Might be by using internal

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

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)