topic Re: Best and Correct way to write this expression in QlikView

Best and Correct way to write this expression

Tue, 22 Apr 2014 03:05:03 GMT

Hello

I am trying to write an expression that returns either a 1 or a 0 which I will multiply by another expression.

Which of the two ways is correct:

Method 1: if (XDate>=StartDate and XDate<=EndDate,1,0)

Method 2: sum(if(XDate>=StartDate and XDate<=EndDate,1,0))

Which of the above two methods is correct?

Please Note I will be multiplying the result of the above expression by my amount. So for example 600 * 1 or 600 * 0 depending upon the condition.

Thanks

Re: Best and Correct way to write this expression

trey_bayne — Tue, 22 Apr 2014 03:14:16 GMT

What are you trying to accomplish. I ask because Method 1 has no aggregation but Method 2 uses sum.

In short, 'If' is always CPU intensive though sometimes necessary. If you have to Sum something, I might recommend using Set Analysis.

sum( {$ < XDate = {">=StartDate<=EndDate"} > } Amount )

Re: Best and Correct way to write this expression

Tue, 22 Apr 2014 03:18:16 GMT

@Trey Bayne

Hello Trey

Basically what I want to do is this:

I have a column Amount and another column is the above expression which should return either a 1 or a 0 to me. Because my third column then is Amount * 1 or Amount * 0 and when it is Amount * 0 then I will supress 0's

Thx

Re: Best and Correct way to write this expression

trey_bayne — Tue, 22 Apr 2014 03:34:01 GMT

In that case, use Method 1.

The sum in Method 2 is not going to accomplish anything as it will just produce the same number as Method 1.

That being said, if you'd be willing to share your 3rd column expression there may be a better way to accomplish what you are doing.

Re: Best and Correct way to write this expression

sujeetsingh — Tue, 22 Apr 2014 03:51:59 GMT

Method 1 seems correct

Re: Best and Correct way to write this expression

vijetas42 — Tue, 22 Apr 2014 04:53:29 GMT

Method 1 is correct way to write this expression

Re: Best and Correct way to write this expression

Greg_Williams — Tue, 22 Apr 2014 04:55:04 GMT

Method 1 (optimized)