From SQL to load script

[fabionogarotto]

Hello all,

I created an SQL code, a sort of view, that extracts all days in the last 365 days, and for each day tells the number of items in storage, e.g.

25/09/2018 - Item1 - 22

25/09/2018 - Item2 - 543

....

26/09/2018 - Item1 - 21

...

This is the SQL script

DECLARE @StartDate datetime = DATEADD(year, -1, GETDATE())
,@EndDate datetime = GETDATE();

WITH theDates AS
(SELECT @StartDate as theDate
UNION ALL
SELECT DATEADD(day, 1, theDate)
FROM theDates
WHERE DATEADD(day, 1, theDate) <= @EndDate
)
SELECT D.theDate, A.IdProdotto, dbo.UT_fnEsistenzaArticolo(A.IdProdotto, D.theDate) as Esistenza
FROM theDates D
CROSS JOIN Articoli A
WHERE A.FlStato = 0
OPTION (MAXRECURSION 0)
;

I use a dbo function to get the number of items for a given day.

I don't want to create a view with this script, because the result is too big.

Would it be possible to add it to the Qlik Sense load script? How?

Does Qlik Sense load script can read dbo functions?

Thank you very much!

Fabio

[NadiaB · Accepted solution]

Hi @fabionogarotto 

Have you seen this post?

https://community.qlik.com/t5/Qlik-Sense-Data-Connectivity/Execute-stored-procedure-with-parameters/...

Kind regards.

[fabionogarotto]

Hi @NadiaB 

thank you, I included my whole script in another stored procedure, and i call it from the load script as

[StorageProducts]:

SQL EXECUTE sp_StorageProducts;