topic Re: If Statement in QlikView

If Statement

Thu, 29 Nov 2012 11:44:02 GMT

I have a if statement that is to look at a number and if the number is above 15 to show the words delayed if it is under 15 or a negative value should display not delayed.

At present it is only displying not delayed for every result. going forward I want to be able to calculate the total amount of delayed and not delayed by day week month and year.

i have attahced a sample of my data where you can see the issue

Re: If Statement

Thu, 29 Nov 2012 11:48:29 GMT

Sorry I should have put the if statement I am currently using:

if($(DELAYMINS)>0.010416666671517,'Delayed','Not Delayed')

Re: If Statement

jagan — Thu, 29 Nov 2012 12:16:37 GMT

Hi,

PFA file.

Regards,

Jagan.

Re: If Statement

Thu, 29 Nov 2012 12:20:38 GMT

Im not able to open up files once you have resaved it as I am using the personal free edition.

please could you submit the changes on here and i will do them within Qlikview

Re: If Statement

jonathan_dau — Thu, 29 Nov 2012 12:26:47 GMT

Hi,

Don't you think it's a problem related to the definition of you variable DELAYMINS ?

If you try DELAYMINS = Minute(Frac(SCH)-Frac(CHKS)) ??

Hope that will help.

Jonathan

Re: If Statement

jagan — Thu, 29 Nov 2012 12:31:15 GMT

Hi,

Try this expression

=if(Minute(Frac(Sum(CHKS))-Frac(Sum(SCH))) >0.010416666671517,'Delayed','Not Delayed')

Regards,

Jagan.

Re: If Statement

Thu, 29 Nov 2012 12:34:32 GMT

Hi Jonathan,

I have changed the variable and so far it seems to work why would that make a difference can you explain?

Re: If Statement

Thu, 29 Nov 2012 12:37:41 GMT

Hi Jagan this has worked correctly thank you.

could you explain why referring to the variable?

Re: If Statement

jonathan_dau — Thu, 29 Nov 2012 12:41:45 GMT

Hi,

First I think your expression was not rightly writen :

Minute(Frac(Timestamp1)-Frac(Timestamp2)) --> Minute(Frac(Timesstamp1)-Frac(Timestamp2))

And after I don't really see the point of puting SCH and CHKS into variables.

But I just had a quick look and I'm not sure it works according to your needs.

Regards

Jonathan

Re: If Statement

Thu, 29 Nov 2012 12:42:52 GMT

Sorry after looking at a larger set of results it is not populating the correct output.

Re: If Statement

Thu, 29 Nov 2012 12:48:09 GMT

Hi you are correct it is not working properly i have took a screen shot The Delayed column uses the altered variable DELAYMINS and the delayed 2 column is using the expression given by Jagan.

Adfjacent to the highlighted cell it should display delayed and a non highlighted cell should display not delayed.

Re: If Statement

jagan — Thu, 29 Nov 2012 12:52:21 GMT

Hi,

Try adding SCH and CHKS as dimensions. I think this solves the issue.

Regards,

jagan.

Re: If Statement

Thu, 29 Nov 2012 12:57:09 GMT

Hi thanks for your response i tried as per what you instructed but to no avail.

Re: If Statement

Thu, 29 Nov 2012 13:00:45 GMT

Hi Jagan,

I added both as dimensions but it did not alter the output. I have reattached the file i am working with.

NOTE: I am unable to load files I havent created as I am using the personal free edition.

Re: If Statement

Fri, 30 Nov 2012 08:49:14 GMT

Hi is anyone able to provide any further tips as to why this if statement is not functioning properly

Re: If Statement

flipside — Fri, 30 Nov 2012 09:34:26 GMT

I may be misunderstanding your requirement but it looks to me that you need some sort of aggregation in your expression (sum, avg, max etc) otherwise you will return null and the comparison will fail hence only one value is returned.

I'm not sure why you are using the variable, but doesn't this work ...

=if(avg(CHKS-SCH)>0.010416666671517,'Delayed','Not Delayed')

I would probably use this instead though ...

=if(avg(CHKS-SCH)*24*60>15,'Delayed','Not Delayed')

flipside

Re: If Statement

Fri, 30 Nov 2012 09:38:42 GMT

tried the second formula and worked thanks so much flipside, and thank you as well to everyone that assisted.