topic Re: Query Prompt in Connectivity & Data Prep

Query Prompt

Sheri — Thu, 16 May 2024 15:30:42 GMT

Can I have a prompt to filter the data when using an SQL query? Something like:

Select * from table where column = prompt

--- where "prompt" is input from the user at run time

Re: Query Prompt

igoralcantara — Thu, 16 May 2024 15:33:21 GMT

Good day. Where are you planning to do this? Qlik Sense load script? Somewhere else?

Re: Query Prompt

Sheri — Thu, 16 May 2024 16:03:26 GMT

Yes in the Qlik Sense Load script. I would like my report to prompt the user and then use that result to run the query for that filter.

Re: Query Prompt

igoralcantara — Thu, 16 May 2024 17:09:37 GMT

You can do something similar using variables.

Option 1: Easy

Set a variable in your script and use it as a prompt

Set vPrompt = value;

Select * from table where column = ‘$(vPrompt)’;

Option 2: Less easy

Set the variable using a input variable custom object. Have a button that reloads the app.

Re: Query Prompt

Sheri — Thu, 16 May 2024 17:28:41 GMT

How does the first option prompt the user?
and if it does not, do you have the steps to do the second option?

Re: Query Prompt

igoralcantara — Thu, 16 May 2024 17:41:40 GMT

To prompt the user you need the option 2.

Re: Query Prompt

Sheri — Thu, 16 May 2024 17:43:52 GMT

I am trying to do option 2, but it is not working. Do you have the steps? Or a video that shows me how?

Re: Query Prompt

igoralcantara — Thu, 16 May 2024 17:58:05 GMT

Sure. Here are some images that show the sequence.

In summary, you need to (after your script is ready to use the variable as my earlier message showed):

All in the sheet design mode.

- Create the variable

- Add an Input variable object and configure it

- Add a button

- Add a Reload action to the button

Re: Query Prompt

Sheri — Thu, 16 May 2024 18:27:03 GMT

That is what I tried before. It does not pass the value (that the user put in the variable) to the query to fetch the data using the prompt value. It just reloads the data using the value that I set in the original query.

This would be my original query on the firs load.

Set vPrompt = 4845;

Select * from table where column = $(vPrompt);

Once the user typed in the Variable Input input box the value of 4851 and presses the button. The page reloads with the 4845 value. I need it to use the 4851 in the query and go get the data for that value.

Re: Query Prompt

igoralcantara — Thu, 16 May 2024 18:36:33 GMT

Remove the Set line, it will override the value set on the UI.

Re: Query Prompt

Sheri — Thu, 16 May 2024 18:51:01 GMT

That worked perfectly, thank you!

Re: Query Prompt

igoralcantara — Thu, 16 May 2024 18:52:29 GMT

Amazing! I am glad I could help.