Discussion Board for collaboration on QlikView Scripting.
I want to load data from stored procedure into qlikview.
the SP gets an input (date) and returns AgencyId and HotelId.
I created an ODBC connection and then:
DECLARE @d date = getdate()-1
EXEC sp_WhiteDots @d;
the sql script works in Microsoft SQL server.
but there are no results in qlikview (no errors either).
i also tried :
load AgencyId, HotelId;
but then i got this error :
Field not found - <AgencyId>
EXEC sp_WhiteDots @d
You could only use these functions and syntax within your sql-statement which the database and the odbc-driver support. I would it try something easier like:
SQL SELECT YourFields From YourProcedure;
I would suggest create a QlikView variable to hold DECLARE @d date = getdate()-1 and pass the variable into your SQL
EXEC sp_WhiteDots @d.
Hope this help
let var= date(today()-1, 'YYYYMMDD');
sql execute sp_WhiteDots '$(var)';
Please see the below script:
As you can see I created a variable called vParam which is equivalent of GETDATE() - 1. Although, pay attention to the date format 'YYYY-MM-DD', this is SQL Server default date format.
The original table had the below data:
After I ran the QV script, I got the below results which gave every record that didn't have today's date:
And here is my stored procedure for this exercise:
hope this helps.