topic Re: Using IF condition where the criteria is NE in QlikView

Using IF condition where the criteria is NE

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

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)?

Re: Using IF condition where the criteria is NE

sunny_talwar — Mon, 24 Oct 2016 16:21:42 GMT

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?

Re: Using IF condition where the criteria is NE

lucasdavis500 — Mon, 24 Oct 2016 16:27:04 GMT

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

Re: Using IF condition where the criteria is NE

hopkinsc — Mon, 24 Oct 2016 16:33:55 GMT

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

sunny_talwar — Mon, 24 Oct 2016 16:45:40 GMT

Can you check by adding floor here?

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

Re: Using IF condition where the criteria is NE

lucasdavis500 — Mon, 24 Oct 2016 16:49:25 GMT

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

Re: Using IF condition where the criteria is NE

sunny_talwar — Mon, 24 Oct 2016 16:55:38 GMT

How about this:

ThirdCET:

LOAD *,

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

Re: Using IF condition where the criteria is NE

lucasdavis500 — Mon, 24 Oct 2016 17:47:55 GMT

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

Re: Using IF condition where the criteria is NE

lucasdavis500 — Mon, 24 Oct 2016 17:50:47 GMT

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

Re: Using IF condition where the criteria is NE

Anil_Babu_Samineni — Mon, 24 Oct 2016 17:54:45 GMT

Might be by using internal

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

Re: Using IF condition where the criteria is NE

lucasdavis500 — Mon, 24 Oct 2016 18:16:57 GMT

Why must you include interval? this didn't seem to work...

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

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

taking away the single quotes around 1000 and 1001...still doesn't seem to resolve the issue...

Re: Using IF condition where the criteria is NE

sunny_talwar — Mon, 24 Oct 2016 18:38:06 GMT

All I was trying to do was to make sure that 1000 is 1000 and not 1000.0000000001 and same for the 1001

Re: Using IF condition where the criteria is NE

lucasdavis500 — Mon, 24 Oct 2016 18:58:01 GMT

=NUM((SUM(IF([Response Time] < 1000, [Response Time])) + SUM(IF([Other Response Time] < 1000, [Other Response Time]))) / (COUNT(Pkey) + COUNT([Other Control Number])), '#,##0.00')

Seemed to do the trick....