Where clause did not filter the data

[Mur]

Hello,

I have this load script for one of my app in Qliksense.

Table1:

Load

First_Name,

Last_Name,

Salary,

Fiscal_Year

SQL Select

FirstName as First_Name,

LastName as Last_Name,

Salary,

FY as Fiscal_Year

From XYZ_Table

In the sheet, I have this simple table with Fiscal_Year column that shows all the data for all the years.

User requested to filter the data to only shows the current year and two previous years. I have adjusted my code to this:

Table1:

Load

First_Name,

Last_Name,

Salary,

Fiscal_Year

SQL Select

FirstName as First_Name,

LasrName as Last_Name,

Salary,

FY as Fiscal_Year

From XYZ_Table

WHERE Fiscal_Year >= (YEAR(GETDATE())-2);   //to get only year 2024 and 2023,2022//

It loaded successfully and I got no errors, but when I go to my table in the sheet, I still see years from 2016-2021

I am not sure what I am missing. Could someone please help me with this?

thank you!

[BrunPierre]

Hi, you can’t use a newly transformed field in the WHERE clause. Furthermore, It's worth considering that the 'FY' column might be stored as a string or character type and must be converted for accurate comparison.

Table1:
Load
First_Name,
Last_Name,
Salary,
Fiscal_Year;

SQL Select
FirstName as First_Name,
LastName as Last_Name,
Salary,
FY as Fiscal_Year
From XYZ_Table
WHERE  Fiscal_Year   FY >= (YEAR(GETDATE())-2);

[Mur]

Thanks for the response @BrunPierre . Here is what I did but still don't get the result that I want.

I want to load the data for the current year and two previous years, but I still see years from 2016-2021 in my visual table.

Table1:

Load

First_Name,

Last_Name,

Salary,

Fiscal_Year

Where Fiscal_Year>=(Year(Today())-2);

SQL Select

FirstName as First_Name,

LastName as Last_Name,

Salary,

FY as Fiscal_Year

From XYZ_Table

WHERE FY >= (YEAR(GETDATE())-2);

[BrunPierre]

You need not apply the condition in both statements, the filtering will be seamless within the load statement.

Table1:
Load
First_Name,
Last_Name,
Salary,
Fiscal_Year
Where Year(Fiscal_Year) >= (Year(Today())-2);

SQL Select
FirstName as First_Name,
LastName as Last_Name,
Salary,
FY as Fiscal_Year
From XYZ_Table;

[Mur]

@BrunPierre I tried that, but it gives me error. The error says (Connector error: Status(StatusCode="InvalidArgument", Detail="ERROR [42000] Incorrect syntax near 'load'., "))

I dont think using this condition Where Year(Fiscal_Year) >= (Year(Today())-2) in load is supported in qliksense scripting.

I used Where clause in my sql query initially to ensure that only the required data is fetched from the database and to avoid slowing down the application. It loaded with no errors, but it didn't give the required result, and it was still showing all the years.

any idea what is wrong with this query?

Table1:

Load

First_Name,

Last_Name,

Salary,

Fiscal_Year;

SQL Select

FirstName as First_Name,

LastName as Last_Name,

Salary,

FY as Fiscal_Year

From XYZ_Table

WHERE FY >= (YEAR(GETDATE())-2);

[marcus_sommer]

Just load year(getdate()) as field within the SQL to see the results. Further make a check what FY container, maybe its a date and formatted as year. 

[Mur]

I run this query in SQL and it gave the correct results.

Select

FirstName as First_Name,

LastName as Last_Name,

Salary,

FY as Fiscal_Year

From XYZ_Table

WHERE FY >= YEAR(GETDATE())

and I see that FY container has the year in year format. 

no idea why Qlik sense does not fetch the data I want!!

[marcus_sommer]

Its not caused from qlik because qlik dont executes any SQL else it Transfers just the string. Therefore its caused from the DB or the driver.

[marcus_sommer]

If it's a rather small data-set you may skip the sql-filter and just doing it within Qlik. But by larger data it will have disadvantages and it's in general more a workaround as a solution.

That's working within the data-base excludes a technically issue on this side and could mean that:

• the wrong data-base or table/view is accessed
• the driver couldn't handle the query properly - maybe simply returning always TRUE by comparing a string against a number respectively values with a different data-type or not knowing getdate() and completely ignoring the statement
• or the underlying data are different to your expectation - a (pre-)view of the data might not display the real data else any kind of data-interpretation

Therefore my first suggestion to remove it from the where-clause and including this and maybe some extended checks in the query, like:

getdate() as a,
getdate() >= FY as b,

and further things like

isnum(), istext(), FY * 1, floor(FY) and similar stuff

and of course adjusted to the appropriate supported sql-functions of the data-base. Making the real existing values and results visible will show why the match didn't work and very probably also valuable hints to the cause.

[joshsiddle8]

It seems like the filter you applied to only show data from the current year and two previous years didn't take effect as expected. One thing to check is the formatting of the Fiscal_Year field in your data source. Ensure that it's formatted as a numeric field rather than a string to ensure accurate filtering based on the year. Additionally, you might want to double-check the syntax of your WHERE clause to ensure it's correctly filtering for years 2024, 2023, and 2022. If the issue persists, feel free to provide more details or code snippets for further assistance.