qlik sense store procedure with variable

[#1]

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?

[Gysbert_Wassenaar]

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)';

[#3]

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'

[Gysbert_Wassenaar]

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

[#5]

unfortunately this extention not added.

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

[#6 · Accepted solution]

I found Calendar_DatePicker and it works fine