Where clause in Qlik Sense Script

[krithikarees]

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)

;

[sunny_talwar]

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)

[krithikarees]

That did not work .

[sunny_talwar]

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?

[krithikarees]

Anything after [Controlling Area_CO_AREA]  is being ignored.

[krithikarees]

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?

[Gabriel]

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]

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)

[sunny_talwar]

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)

[krithikarees]

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