topic Re: qlik sense store procedure with variable in QlikView

qlik sense store procedure with variable

Wed, 13 Jul 2016 11:07:44 GMT

Hi!

I have problem with load data from store procedure.

like this works fine:

-------------------------------------------------------------------------

load

SQL

exec TS.dbo.P_profil_fil '20160601','20160630';

------------------------------------------------------------------------

But when i try use variable from qlik sense

vNP = date(min([date task]),'YYYYMMDD')

v KP = date(max([date task]),'YYYYMMDD')

and load script:

load

SQL

exec TS.dbo.P_profil_fil '$(vNP)','$(vKP)';

not working and looks like:

exec TS.dbo.P_profil_fil 'date(min([date task]),''YYYYMMDD'') ','date(max([date task]),''YYYYMMDD'') '

but i need value in argument not formula

what i missed?

Re: qlik sense store procedure with variable

Gysbert_Wassenaar — Wed, 13 Jul 2016 11:51:16 GMT

Perhaps like this:

Temp:

LOAD

Date(min(FieldValue('date task',RecNo()),'YYYYMMDD') as Min

Date(max(FieldValue('date task',RecNo()),'YYYYMMDD') as Max

Autogenerate FieldValueCount('date task');

LET vNP = peek('Min',-1,'Temp');

LET vKP = peek('Max',-1,'Temp');

DROP TABLE Temp;

MyTable:

SQL EXEC TS.dbo.P_profil_fil '$(vNP)','$(vKP)';

Re: qlik sense store procedure with variable

Wed, 13 Jul 2016 12:22:13 GMT

Hi, gwassenaar !

Thank you for your response.

vNP and vKP are variables that fills by user. User select date task field and variables set up automatically.

Then i use Reload button to reload document with new period.

In qlikview i used calendar and got value (date) directly then i did

let vNP = Date(vNP,'YYYYMMDD');

let vKP = Date(vKP,'YYYYMMDD');

load

SQL

exec P_profil_fil '$(vNP)','$(vKP)';

but in qlick sense i couldn`t find how to pass value for vNP like '20160713'

Re: qlik sense store procedure with variable

Gysbert_Wassenaar — Wed, 13 Jul 2016 13:14:19 GMT

In qlikview i used calendar and got value (date) directly then i did

let vNP = Date(vNP,'YYYYMMDD');

let vKP = Date(vKP,'YYYYMMDD');

Yes, these variables then contain a date value, not an expression that can't be evaluated during the script execution outside a load statement. The expression date(min([date task]),'YYYYMMDD') can't calculate the min of date task unless it's used in a load statement. Perhaps you can use an extension like this: GitHub Variable-Calendar

Re: qlik sense store procedure with variable

Wed, 13 Jul 2016 13:37:38 GMT

unfortunately this extention not added.

Thank you for direction in my problem i`ll try to find another.

Re: qlik sense store procedure with variable

Thu, 14 Jul 2016 07:43:40 GMT

I found Calendar_DatePicker and it works fine