topic Re: Excluding field values in script in QlikView

Excluding field values in script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have a table which contains Status,Operational Date,Cancellation Date.In Status column some sites are operational and some are disconnected,also some sites which have cancellation date in current month are also shown Disconnected.Now I am excluding Disconnected sites in script itself by giving where Status<>'Disconnected',but,I need values from sites whose status is disconnected but cancellation date is of current month.Please help me in solving this issue.Thank you in advance

Re: Excluding field values in script

Anil_Babu_Samineni — Wed, 08 Feb 2017 10:45:13 GMT

Would you post the script on wall and meantime few rows to handle it

Re: Excluding field values in script

Anonymous — Wed, 08 Feb 2017 11:09:59 GMT

Hi Anil

posted sample data,here I used where Status<>'Disconnected' in script,but,I need values where status is disconnected and cancellation date is of particular month form where value is taken,i.e., row-4 and 5 values have to be read and row-6 can be omitted

Re: Excluding field values in script

tresB — Wed, 08 Feb 2017 11:43:06 GMT

Try like:

Where not (status='Disconnected' and month(today())<>Month([Cancellation Date]));

For Jan comparison try like:

Where not (status='Disconnected' and month(addmonths(today(),-1))<>Month([Cancellation Date]));

Re: Excluding field values in script

surendraj — Wed, 08 Feb 2017 12:11:20 GMT

Based on your requirement,Exrtracted only row-4 and 5.

LOAD Region,

Country,

City,

SiteID,

AccountNumber,

status,

[Operational Date],

[Cancellation Date],

Model,

Desc,

[Local Currency],

Amount

FROM

(ooxml, embedded labels, table is Sheet1)

Where status ='Disconnected' and Month([Cancellation Date])= month(addmonths(today(),-1));

Re: Excluding field values in script

Anonymous — Wed, 08 Feb 2017 12:15:52 GMT

Hi Tresesco

I also have some other filters like where country<>'xyz' then how can I write this where statement,also if I need sites from Nov-2016

Re: Excluding field values in script

tresB — Wed, 08 Feb 2017 12:25:45 GMT

It is all about combining AND, OR, NOT properly. The above logic implementation would give you an idea. For specific and exact solution you have to provide with all the conditions at a time.

Re: Excluding field values in script

Anonymous — Wed, 08 Feb 2017 12:41:57 GMT

My conditions are: where Country<>'' and Country<>'xyz' and Status<>'Disconnected' and len(trim([Account Number])) > 0);.I gave like this,now I have to get sites which are disconnected but cancellation date is of particular month(November) ,but, completely disconnected sites shouldn't show as mentioned in above where clause

Re: Excluding field values in script

Anonymous — Wed, 08 Feb 2017 12:46:03 GMT

Hi Surendra

Please check all my conditions mentioned in this post(where Country<>'' and Country<>'xyz' and Status<>'Disconnected' and len(trim([Account Number])) > 0)) also what should I have to do if data should be for November-2016

Re: Excluding field values in script

tresB — Wed, 08 Feb 2017 12:56:55 GMT

Try like:

Where Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

not (status='Disconnected' and month(addmonths(today(),-1))<>Month([Cancellation Date]));

Re: Excluding field values in script

Anonymous — Wed, 08 Feb 2017 14:40:23 GMT

Hi Tresesco

Now that I understood how to write Expression,but,it is not giving values of sites which are disconnected in November and December when I am deriving values form that fields.Please help.how can I modify above expression so that I can get values for Nov and Dec fields.I think i need to use ADDYEARS function but not sure how to do

Re: Excluding field values in script

Anonymous — Wed, 08 Feb 2017 14:55:38 GMT

is it something like

Where Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

not (Status='Disconnected' and (month(AddMonths(today(),-1))<>Month([Cancellation Date])) and (Year(AddYears(Today(),-1))<>Year([Cancellation Date])))

Re: Excluding field values in script

Fri, 17 Feb 2017 16:27:08 GMT

Hey Vani,

where not isnull(Country) and Country<>'xyz' and len(trim([Account Number]) > 0) and not (match(Status,'Disconnected') and (month(AddMonths(today(),-1))<>Month([Cancellation Date]))

and (Year(AddYears(Today(),-1))<>Year([Cancellation Date]))))

Re: Excluding field values in script

Anonymous — Tue, 21 Feb 2017 07:10:58 GMT

I have tried this expression and it is working fine.Thank you all for your help.

Where Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

(Status='Operational' or (Status='Disconnected' and

([Cancellation Date]>=MonthStart(Today(),-3) and [Cancellation Date]<=MonthEnd(Today(),0) and

([Cancellation Date]>=YearStart(Today(),-1) and [Cancellation Date]<=YearEnd(Today(),0))))) ;