topic Re: User Input Variable direct to WHERE statement in SQL load query in QlikView

User Input Variable direct to WHERE statement in SQL load query

Cassidyd90 — Mon, 19 Sep 2022 10:07:44 GMT

Hi,

I have SQL load scripts with 2 fields in the WHERE part. Is there a way to get user input (multiple strings) into the script, then reload app?

I guess it would be by using variables like

WHERE material_id IN (

inputVar1

)

AND Location_ID IN (

inputVar2

)

Re: User Input Variable direct to WHERE statement in SQL load query

MendyS — Mon, 19 Sep 2022 10:36:41 GMT

Hi @Cassidyd90

you can enter the value of inputVar1/2 into Excel files and load the excel to model before the SQL load,
if that option possible for you I can expand on that if necessary

Re: User Input Variable direct to WHERE statement in SQL load query

Cassidyd90 — Mon, 19 Sep 2022 11:06:42 GMT

Hi MendyS,

I see what you mean and yes that may be a solution.

The complication is that I have multiple SQL queries to draw info from cloud database.

Each query will have the var1/2 input (material_id + location_id) but I would prefer not to join the tables in Data Model because they are for very different reasons/checks and will return an enormous amount of extra rows.

It would be 6 SQL queries = 6 tables (unlinked) with 6 sheets representing findings.

Any idea how to go about that?

*Important that I only load tables with data that matches input or else will have millions of rows in each table.

Thanks for you help.

Re: User Input Variable direct to WHERE statement in SQL load query

Cassidyd90 — Mon, 19 Sep 2022 13:22:40 GMT

I think I got it solved.

1 : Create two variables (vMaterial_ID , vLocation_ID) in sheet

2 : In sheet use extension 'Variable input', make two seperate inputs and assign one to each variable.

3: In Data Load Editor in each SQL query have 'WHERE material_ID in ($(vMaterial_ID)) AND location_Id in ($(vPlant_ID))

It worked and only retrieved rows where search matched what I put in variable.

*A caveat more towards the SQL query is they have to be prepared 'like','this',with','commas' and apostophes. So it will be one huge string in the input variable box on the sheet.