topic Re: Exclude rows from SQL Data with multiple conditions in QlikView

Exclude rows from SQL Data with multiple conditions

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

Hello Qlik Experts,

I am new to Qlik, I am trying to pull the data SQL and store into QVD's. In the preceding load I need to apply certain condition in order to exclude some unnecessary rows and they have some conditions to be excluded. Below is the example script I'm using to exclude the rows.

LOAD

SQL SELECT *

FROM "Number".dbo."Equation"

WHERE not(1='0' and 2='0' and 3='0' and 4='0' and 5='0' and 6 < Date(MonthStart(AddMonths(Today(), -37)), 'YYYY-MM-DD'));

For Column 6(it is a date column) the condition is it should not load data which is 37 months old and this should be recurring.

Just want to know If I am right with my script and please suggest if you any good ideas to do so??

Thanks in Advance

Re: Exclude rows from SQL Data with multiple conditions

sunny_talwar — Wed, 17 Oct 2018 18:06:12 GMT

Why don't you exclude the data in the sql itself? This will help you bring less number of rows from sql itself. If you still want to do it in Load, then you need to put your where statement before Select

LOAD

WHERE not(1='0' and 2='0' and 3='0' and 4='0' and 5='0' and 6 < Date(MonthStart(AddMonths(Today(), -37)), 'YYYY-MM-DD'));

SQL SELECT *

FROM "Number".dbo."Equation";

Re: Exclude rows from SQL Data with multiple conditions

CVR — Wed, 17 Oct 2018 18:37:02 GMT

Thanks for quick reply Sunny. You are right ,I can exclude the DATA in SQL but unfortunately I am not allowed to do so in our SQL DB. I will reload the QVW with your above script and will let you know the result. Can you also please confirm if the below script is correct?

LOAD

OrderDate,

Quantity

WHERE NOT (OrderDate < Date(MonthStart(AddMonths(Today(), -37)), 'YYYY-MM-DD'))

SQL SELECT *

FROM "Sales".dbo."Product";

Script looks good for me don't know why I am getting an error message (red line)

Thank you

Re: Exclude rows from SQL Data with multiple conditions

sunny_talwar — Wed, 17 Oct 2018 18:55:57 GMT

You are missing a semi colon

LOAD

OrderDate,

Quantity

WHERE NOT (OrderDate < Date(MonthStart(AddMonths(Today(), -37)), 'YYYY-MM-DD'));

SQL SELECT *

FROM "Sales".dbo."Product";