Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
krithikarees
New Contributor III

Where clause in Qlik Sense Script

Hello All,

I am noticing that only the first where clause condition is being executed and the rest is ignored. Is there a reason why Qlik Sense does this? Or is there anything wrong with my script?

Thanks in advance

Load

..

...

......

...........

From (lib://qvdstage1/abcd/accc.QVD)(qvd)

Where

[Controlling Area_CO_AREA]= 'VMS1' or '#' and

[Chart of accounts_CHRT_ACCTS] = 'VSM1' and

[Fiscal Year Variant_FISCVARNT] = 'V8' and

([Clearing Date]='' or num(Today(2))- num([Clearing Date]) < 90)

;

1 Solution

Accepted Solutions
Highlighted

Re: Where clause in Qlik Sense Script

How about when you use just this

Where

[Chart of accounts_CHRT_ACCTS] = 'VSM1' and

[Fiscal Year Variant_FISCVARNT] = 'V8' and

([Clearing Date]='' or num(Today(2))- num([Clearing Date]) < 90)

What do you get?

View solution in original post

11 Replies
Highlighted

Re: Where clause in Qlik Sense Script

Try this

Where

Match([Controlling Area_CO_AREA], 'VMS1', '#') and

[Chart of accounts_CHRT_ACCTS] = 'VSM1' and

[Fiscal Year Variant_FISCVARNT] = 'V8' and

([Clearing Date]='' or num(Today(2))- num([Clearing Date]) < 90)

Highlighted
krithikarees
New Contributor III

Re: Where clause in Qlik Sense Script

That did not work .

Highlighted

Re: Where clause in Qlik Sense Script

So, when you say ignored, you mean [Controlling Area_CO_AREA] is filtering based on just VMS1 and not based on # or do you mean that anything after the first line is completely ignored?

Also, this new script behaves exactly like what you had before?

Highlighted
krithikarees
New Contributor III

Re: Where clause in Qlik Sense Script

Anything after [Controlling Area_CO_AREA]  is being ignored.

Highlighted
krithikarees
New Contributor III

Re: Where clause in Qlik Sense Script

The expression you gave behaves the same. it only takes only the first line and ignores the rest - gives me the same result.

is the and being ignored?

Highlighted
Partner
Partner

Re: Where clause in Qlik Sense Script

Hi

Can I suggest you look into operator precedence how it works.

In your where clause ([Clearing Date]='' or num(Today(2))- num([Clearing Date]) < 90) will be perform first before the rest of the where clause, regardless of how you structure your where clause. I think you need to look into your operator precedence steps

[Controlling Area_CO_AREA]= 'VMS1' or '#' and

[Chart of accounts_CHRT_ACCTS] = 'VSM1' and

[Fiscal Year Variant_FISCVARNT] = 'V8' and

([Clearing Date]='' or num(Today(2))- num([Clearing Date]) < 90)

Also I don't think [Clearing Date]='' will work maybe replace that part with ISNULL([Clearing Date])

sasiparupudi1
Honored Contributor III

Re: Where clause in Qlik Sense Script

is there a correlation between

[Controlling Area_CO_AREA]= 'VMS1' and

[Chart of accounts_CHRT_ACCTS] = 'VSM1'  ?

May be you should try commenting the first part of the where condition to see if you get any results back

Where

//Match([Controlling Area_CO_AREA],'VMS1','#' ) and

[Chart of accounts_CHRT_ACCTS] = 'VSM1' and

[Fiscal Year Variant_FISCVARNT] = 'V8' and

([Clearing Date]='' or num(Today(2))- num([Clearing Date]) < 90)

Highlighted

Re: Where clause in Qlik Sense Script

So all of these are ignored?

[Chart of accounts_CHRT_ACCTS] = 'VSM1' and

[Fiscal Year Variant_FISCVARNT] = 'V8' and

([Clearing Date]='' or num(Today(2))- num([Clearing Date]) < 90)

Highlighted
krithikarees
New Contributor III

Re: Where clause in Qlik Sense Script

Yes Sunny you are right. all those are being ignored.