topic Re: Date Piker and SQL query in App Development

Date Piker and SQL query

OlegTom — Fri, 03 Jun 2022 07:47:06 GMT

How can I get the value of the selected date in Date Piker and then insert it in the SQL query to pass it as a procedure parameter?

Re: Date Piker and SQL query

Andrei_Cusnir — Fri, 03 Jun 2022 14:16:31 GMT

Hello,

I am not 100% sure what is your exact use case scenario, however based on the information that you shared I believe that this use case scenario is not possible.

Allow me to elaborate in detail to my hypothesis:

SQL statemen is used in Data load editor (which is found in the Prepare section) to load data from data sources, while Date Picker is an object found in the Analyze section, where the data is already loaded.
Prepare section and Analyze section are not linked directly. The first one is responsible for loading the data and preparing it, while the second one is used to analyze and present it
Date Picker object is associated with a filed. So if you want to get the selected value, you can use the function "GetFieldSelections(Date_Field)", and it will show you the selected values that you picked in Date Picker such as "1/1/2022, 1/2/2022" etc. However this function is not going to work in Data load editor so you could use the values in the SQL script.
You can also confirm this by taking a look in the official documentation [1], where it is stated that this function is a chart function and not a script function.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members.

---

[1] https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunctions/getfieldselections.htm

Re: Date Piker and SQL query

OlegTom — Fri, 03 Jun 2022 23:01:04 GMT

I created two variables in the worksheet, and got the start and end dates from the Data Picker, but how do I get these dates in the script?

My Script in bottom:

SET vQVDPath = 'D:/QlikSense';

LET sLocalTimeZone = TimeZone();
LET sStartDate = Date(Min([%Master Calendar Date])); //'Kyiv'
LET sEndDate = Date(Max([%Master Calendar Date]));

LET Str = 'EXEC dbIdc.dbo.sp_EmsGetMVAData ''2022-05-24 00:00:00'', ''2022-05-25 23:59:59'', ''CNT_003-FT01''';
// 'EXEC dbIdc.dbo.sp_EmsGetMVADatav '''& [sStartDate] &''', '''& [sEndDate] &''','&'''CNT_003-FT01''';

LIB CONNECT TO 'PLANTIT';

MVA:
LOAD *;
SQL $(Str);
STORE * FROM MVA INTO 'lib://QVD/MVA.qvd';

Re: Date Piker and SQL query

vinieme12 — Tue, 07 Jun 2022 06:45:20 GMT

You might want to use ODAG

https://www.youtube.com/watch?v=Ft3I00-g4H4