topic Re: Using WHERE with AND clause in QlikView

Using WHERE with AND clause

Mon, 15 Dec 2014 15:29:48 GMT

Hello,

I am trying to use a WHERE clause with an AND clause to achieve the following:

Actual Volume should only be loaded if >= 0.

No data should be loaded if Forecast and Actual both = 0.

I have tried the following but it seems to eliminate records where Forecast = 0 but Actual does not.

WHERE [Actual Volume] >='0' AND ([Actual Volume] <> '0' AND [Forecast Volume]<> '0');

I'm not sure if I'm perhaps using AND incorrectly or misplacing my ().

Thanks for your help!

Re: Using WHERE with AND clause

its_anandrjs — Mon, 15 Dec 2014 15:34:40 GMT

Use for Actual data to be load

Load * From Source

WHERE [Actual Volume] >= 0

Load * from Source

Where ([Actual Volume] <> 0 AND [Forecast Volume]<> 0);

Regards

Anand

Re: Using WHERE with AND clause

cwolf — Mon, 15 Dec 2014 15:52:00 GMT

Hello,

you can take:

WHERE [Actual Volume] >='0' AND NOT ([Actual Volume] = '0' AND [Forecast Volume] = '0');

which is the same as:

WHERE [Actual Volume] >='0' AND ([Actual Volume] <> '0' OR [Forecast Volume] <> '0');

Regards

Christian

Re: Using WHERE with AND clause

ThornOfCrowns — Mon, 15 Dec 2014 16:00:32 GMT

WHERE [Actual Volume] >='0' AND ([Actual Volume] <> '0' is a contradiction. The first part checks for greater or equal to 0 and the secend part checks for not equal to 0. If the condition is 0 your AND cannot resolve itself.

Re: Using WHERE with AND clause

its_anandrjs — Mon, 15 Dec 2014 20:50:04 GMT

Hi,

You can try different way also by this expression load where condition in two different table and then concatenate those

MainTable:

Load * From QVD

Where [Actual Volume] <> 0 AND [Forecast Volume]<> 0;

Concatenate

Load * From QVD

Where [Actual Volume] >=0;

Regards,

Anand

Re: Using WHERE with AND clause

Wed, 17 Dec 2014 11:31:11 GMT

Thanks, Christian!

WHERE [Actual Volume] >='0' AND ([Actual Volume] <> '0' OR [Forecast Volume] <> '0'); seems to work as anticipated