Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Excluding field values in script

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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))))) ;

View solution in original post

13 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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

tresesco
MVP
MVP

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]));

surendraj
Specialist
Specialist

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

1.png

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));

Anonymous
Not applicable
Author

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

tresesco
MVP
MVP

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

tresesco
MVP
MVP

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]));