topic Re: How to pass multiple values from filter to a SQL query in data load script? in App Development

How to pass multiple values from filter to a SQL query in data load script?

satya_s — Fri, 29 Dec 2023 15:30:22 GMT

Hi,

I have the following requirement:

1.User selects Year (single value) and Month (multiple values) from the filters in a sheet

2.User clicks on Reload button (Button Chart Type), which then passes the values to the Data Load scripts

3.The SQL query in Data Load script has 'Year' and 'Month' in the WHERE condition and single/multiple values needs to be passed. I guess it would be something like this.

SELECT COUNT(OrderID) NoOfOrders, SUM(OrderAmount) OrderAmount
FROM Orders
WHERE TO_CHAR(OrderDate,'YYYY') = '$(vYearID)'
AND TO_CHAR(OrderDate,'MM') IN '$(vMonthID)'

Would the IN condition work in the SQL query, if the vMonthID is populated as 9,10,11?

Can someone please share the steps or a sample file for reference?

Many Thanks !!

Re: How to pass multiple values from filter to a SQL query in data load script?

satya_s — Fri, 29 Dec 2023 19:08:26 GMT

Hi,

Please note that I am able to refresh the data in the SQL when there is only 1 value in the variables vYear and vMonth variables. The following works...

vYear = GetFieldSelections(YearID);
vMonth = GetFieldSelections(MonthID);
The above variables are set in the Reload button configuration under 'Actions and Navigation'.

SELECT COUNT(OrderID) NoOfOrders, SUM(OrderAmount) OrderAmount
FROM Orders
WHERE TO_CHAR(OrderDate,'YYYY') = $(vYear)
AND TO_CHAR(OrderDate,'MM') = $(vMonth)

The issue comes when there are multiple values selected from the filter. For example if I select Months 9,10 and 11 from the filter then the SQL query fails on click of Reload button because the IN condition doesn't work.

SELECT COUNT(OrderID) NoOfOrders, SUM(OrderAmount) OrderAmount
FROM Orders
WHERE TO_CHAR(OrderDate,'YYYY') = $(vYear)
AND TO_CHAR(OrderDate,'MM') IN $(vMonth)

How can I make the SQL to work with a IN condition when the variable vMonth is having multiple values?

Kindly help.

Re: How to pass multiple values from filter to a SQL query in data load script?

Bjorn_Wedbratt — Sat, 30 Dec 2023 14:09:55 GMT

Hi @satya_s

Try using the CONCAT() function to get all values in the month field into a comma separated list and pass it in the IN statement. See if that works

vMonth = concat(GetFieldSelections(MonthID),',')

Best

Björn

Re: How to pass multiple values from filter to a SQL query in data load script?

anat — Sun, 31 Dec 2023 04:00:18 GMT

use concat function to store multiple values in variable

Concat(DISTINCT GetFieldSelections(Month),', ')

https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/StringAggregationFunctions/concat.htm#:~:text=Concat()%20is%20used%20to,expression%20evaluated%20over%20each%20dimension.&text=The%20expression%20or%20field%20containing%20the%20string%20to%20be%20processed.&text=Each%20value%20may%20be%20separated%20by%20the%20string%20found%20in%20delimiter.

Re: How to pass multiple values from filter to a SQL query in data load script?

marcus_sommer — Tue, 02 Jan 2024 14:11:47 GMT

It depends mainly on the from the data-base supported syntax. I suggest you comment the month-variable at first and writes two month manually within the in() or in "...." or whatever the data-base recognized. If it worked you shows the variable-content within a text-box until it looked like your hard-coded values.

Re: How to pass multiple values from filter to a SQL query in data load script?

satya_s — Tue, 02 Jan 2024 14:28:53 GMT

Hi @anat

Thanks for your reply. When I am selecting the filter, the multiple values are already comings with a comma delimiter. The issue was with the SQL formation.

The curly brackets after the IN statement needs to be explicitly defined for the SQL to work. I changed the SQL query to the one below and then it worked.

SELECT COUNT(OrderID) NoOfOrders, SUM(OrderAmount) OrderAmount
FROM Orders
WHERE TO_CHAR(OrderDate,'YYYY') = $(vYear)
AND TO_CHAR(OrderDate,'MM') IN ( $(vMonth) )

Thanks once again for looking into it.

Re: How to pass multiple values from filter to a SQL query in data load script?

satya_s — Tue, 02 Jan 2024 14:30:13 GMT

Hi Marcus,

Thanks for your reply. I managed to make it work by explicitly putting the curly brackets in the SQL.

SELECT COUNT(OrderID) NoOfOrders, SUM(OrderAmount) OrderAmount
FROM Orders
WHERE TO_CHAR(OrderDate,'YYYY') = $(vYear)
AND TO_CHAR(OrderDate,'MM') IN ( $(vMonth) )

Thanks once again for looking into it.