Dynamically change SQL where clause with user input

[parria1]

I am building an analytic app at the end of an address/phone match workflow that is already built. After the that piece finishes, I need the account numbers found from the match process to be ran through our data warehouse in a query that will fetch the volume and revenue for the accounts.

I am trying to build this last piece alone before adding it to the match workflow.

So far I have been able to run the account numbers in a SQL query using a Dynamic input tool and the "update where clause" option and successfully query the list of account numbers.

However, I also need to change the date parameter. I was trying to make this user input. Can i do this with the one Dynamic input tool i already have? it doesn't seem to read the action tool with text input that i have connected to it.

since the dynamic input tool doesn't understand "BETWEEN" in the where clause, i changed it so it's >=start date and <=end date.

the tool successfully recognizes those as two separate where clauses that can be changed.

How do i get the app to run so the user is asked for the start and end date range and those update the sql?

[steven4320555]

Hi @parria1 , this is something I have done recently.

I believe there are multiple ways of doing it, for me, I used replace a specific string option.

(Construct the target query based on user input, and replace part or entire query as needed to form your target query.)

 Hope it will work for you. 

[danilang]

Hi @parria1 

Can you post a screen shot of the relevant part of your workflow?  Also one for the configuration of the Action tool.

Thanks

Dan

[parria1]

yes, that is what i am trying to exactly, and i do not know how to do this. (Construct the target query based on user input, and replace part or entire query as needed to form your target query.) what is a target query? how do i construct one?

[parria1]

The problem is i do not  know what to do with the action tool. i don't understand how to pass the user input to the modify sql in the action tool. or do i need to do it in dynamic output?

[danilang]

Hi @parria1 

Here's one way to do it. 

Define your start and end dates in a Text Input tool.  Drag two Date tools from the Interface palate.  Connect these both to the lightning bolt on Text input.  The Action tools will appear between Date and Text Input tools.  Modify the StartDate action tool to change the first cell and the EndDate Action tool to modify the second cell.  

When you run the workflow using the Magic Wand beside the Run button, you'll see the interface for the two Date tools.  The dates that you pick here will flow through your workflow.   Use these two date values as input to your Dynamic Input and use them in your Replace functions

Dan

[parria1]

Thank you.

how do i connect this action piece with my workflow? it's not letting me connect the two pieces so i can use the user input as the input for  my dynamic replace.

[parria1]

This partially works until it does not. I am having the following issues:

1. The prompt displays to choose the start and end date-great

2. The first few times i ran this it would run it for the dates selected for one account only but the output would only show in the app output window and the Browse tool would be blank. i need this data to be output into an Excel file in an output tool. where does the data go?

3. The union tool allows me to add the account number field to the Dynamic input tool to replace that filter in the where clause but it would only read the default account number in the query, not cycle through the accounts in the file. It does cycle through the accounts in the file and pull the data for them but NOT in the analytic app..

4. after trying to run this a few times i receive a "No Output" error from the App and this error:

Warning: Dynamic Input (8): The file "32bit:湡㉴椠⁳潮⁴浩汰浥湥整⁤湩琠楨⁳敶獲潩nyxbe2" has a different number of fields than the 1st file in the set and will be skipped

[danilang · Accepted solution]

Hi @parria1 

Glad to get you started.  Instead of using a Union tool to join the accounts and the dates, use an Append Fields tool with the accounts in the T input and the dates in the S input.  This will add the two dates to all the records in dataset.  To output to an excel file, add an Output Data tool to write the file and then make sure that the tool is selected in the config page of the Interface Designer. 

To learn more about using the interface tools, look at the Creating Interactive Apps interactive training

Dan

[parria1]

Thank you! it works now. 

I am not sure what configuration window you have open in that screenshot but i did get the output i wanted in the Excel file after replacing the browse with an output tool.