topic Re: SQL Load Script using Filter List from Excel in Connectivity & Data Prep

SQL Load Script using Filter List from Excel

mskusace — Mon, 13 Dec 2021 17:33:19 GMT

I have an ODBC connection to a database, and I have developed a query to extract data from this database into Qlik Sense. In my "WHERE IN" clause, there is a rather large filter list. I would like to use either an Excel spreadsheet or another source to house the filter list and the SQL query to pull in that list in the "WHERE IN" clause. The filter list is manually changed and I don't want to keep having to migrate the app to our production environment if the app can automatically refresh the SQL query and pull in the filter list from a separate source.

Please let me know if that doesn't make sense or if you need further clarification.

Re: SQL Load Script using Filter List from Excel

dplr-rn — Fri, 26 Jul 2019 16:03:25 GMT

Makes sense. Whats the issue you are facing?

Re: SQL Load Script using Filter List from Excel

mskusace — Fri, 26 Jul 2019 16:09:19 GMT

I have a SQL query with changing filters.

Within my "WHERE IN" clause, the 'Value 1', 'Value 2', and 'Value 3' are always changing and varying in amounts. I have about 500 items in my "WHERE IN" clause and that gets manually updated from time to time. I was hoping to pull in the values in there "WHERE IN" filter from another source, such as a spreadsheet that is located on our network drive.

Currently, any changes I make in the query in the development environment, I have to request a migration to production in my organization. It would be great to have my app on an hourly refresh to rerun the SQL query to load in the latest data and then the "WHERE IN" values are pulled from an Excel spreadsheet located on the network drive so that can get updated and pulled in with the hourly refresh.

Basic Example:

SELECT *

FROM TABLE T

WHERE T.FIELD1 IN (

'Value 1'

,'Value 2'

,'Value 3'

)

Re: SQL Load Script using Filter List from Excel

dplr-rn — Fri, 26 Jul 2019 16:19:58 GMT

As i said it makes sense and should work.

load the filter list into a variable like

'Value 1' ,'Value 2' ,'Value 3'

and use it in the query

Re: SQL Load Script using Filter List from Excel

mskusace — Fri, 26 Jul 2019 16:31:20 GMT

What would the syntax be?

SET FilterList =

LOAD

[List]

FROM SharedDrive\\ExcelSpreadsheet.xlsx

SQL * SELECT

FROM TABLE

WHERE T.FIELD IN (

FilterList

);

That is some pseudo syntax, since I haven't used the variable in the load editor and especially for values located in a file.

Re: SQL Load Script using Filter List from Excel

dplr-rn — Fri, 26 Jul 2019 18:53:05 GMT

roughcode below.

on the road so cant try it out

List:

LOAD

[List]

FROM SharedDrive\\ExcelSpreadsheet.xlsx;

Let vFilterList = peek('List',0,'List');

SQL * SELECT

FROM TABLE

WHERE T.FIELD IN (

$(vFilterList)

);

Re: SQL Load Script using Filter List from Excel

mskusace — Fri, 26 Jul 2019 20:29:30 GMT

A co-worker showed me a solution!

$(Include=lib://SharedDrive/Code.SQL

Then Qlik Sense just executes that code so I can edit the code however I want.

Re: SQL Load Script using Filter List from Excel

nate_ak — Sat, 27 Jul 2019 00:04:36 GMT

I would suggest making it a two step process. Create a table in the data load editor with your SQL, minus the Where clause. Then create a second table by loading your list of filter values. Finally, use these two temp tables in the data load editor to create a third table. Join them together with an inner join. Drop the two temp tables and use the third table as your data source. The inner join will remove all records from the SQL table that don't exist in the filter value table.

Re: SQL Load Script using Filter List from Excel

mskusace — Mon, 29 Jul 2019 14:40:15 GMT

Thanks for posting a solution! That definitely does seem like a viable solution.

I've found the solution I posted to be more efficient for my needs and with the current processes established for updating my dashboards. I will that in mind for future development though.