topic Excluding values from script in QlikView

Excluding values from script

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

Hi All

I have a file with status,end date and amount columns,here status is Operational and disconnected.My requirement is I need amount from rows where status is operational and also where status is disconnected but end date is of nov, dec and jan and I don't need other columns where status is disconnected and end date is of oct or other.

I used expressions like :

and (Status='Operational'

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

Can anyone please help me?

Re: Excluding values from script

Chanty4u — Wed, 15 Feb 2017 10:05:50 GMT

try this

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

(Month(AddMonths(today(),-3))<>Month([End Date]) and (Year(AddYears(Today(),-1))<>Year([End Date])))));

Re: Excluding values from script

MayilVahanan — Wed, 15 Feb 2017 10:18:43 GMT

Try like this

and Match(Status, 'Operational', 'Disconnected') and Match(Month(EndDate), 'Jan', 'Nov','Dec') and (Year(AddYears(Today(),-1))<>Year([End Date])

Re: Excluding values from script

Anonymous — Wed, 15 Feb 2017 11:25:53 GMT

Hi Chanty

When I am using this expression it was throwing an error like if will take 2,3 parameters and I modified it like

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

(Month(AddMonths(today(),-3))<>Month([Cancellation Date]) and (Year(AddYears(Today(),-1))<>Year([Cancellation Date])))) but it is not returning values.Please help

Re: Excluding values from script

Anonymous — Wed, 15 Feb 2017 11:35:25 GMT

I have modified the expression as (Status='Operational' or (Status='Disconnected' and

(Month(AddMonths(today(),-3))=Month([Cancellation Date]) and (Year(AddYears(Today(),-1))=Year([Cancellation Date]))))) and Status<>'TBD' and it is working for November,but I need Dec and Jan data also, do I need to add another addmonths along with this or is there any way to use monthstart or monthsstart to give 3 months data at once.Anyone please reply

Re: Excluding values from script

sasikanth — Wed, 15 Feb 2017 11:35:26 GMT

May be like this

LOAD

FROM Table

WHERE Wildmatch(Status,'*Operational*', '*Disconnected'*) AND

(End_Date>=MonthStart(Today(),-3) and End_Date<=MonthEnd(End_Date,0));