Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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);
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);
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;
@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);
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.
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!!
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.
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:
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.
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.