Update Specific Value in Query from File Input

Question

Hi Folks - I am working on a workflow template that will be reused with a different file inputs and outputs based on each program it is set up for. Right now, each time I build a workflow off of this template, I manually update the values in the WHERE part of the queries to reflect the specific program. I have used the user interface tools to enter the values in the text box that then update the specific values in the query - the problem with this is that the replaced values do not hold when the workflow goes through daily scheduled refreshes. How can I input one file at the time of set up that can be referenced to update the specific values in the query?

Open to any feedback or suggestions.

2023-11-08_12-32-38.png

cjaneczko · Accepted Answer

I think you want to start by looking at the Dynamic Input Tool. This will allow you to update the WHERE clause in a statement. You would setup your query as normal under the input edit at the top. I usually write a statement like this:

where TABLE.FIELD in ('Update', 'This')

Then when you add the condition it will look something like this. In my example below, the Concat_Material is a concatenated list of all of the materials that will be substituted in my IN clause of the SQL statement. I had used a Summarize tool prior to that. I don't think its necessary that its used, but i had some other issues in my workflow that I needed to add it, in order for it to work properly.

sayres · Accepted Answer

Thank you! The Dynamic Input Tool was exactly what I needed. One variation to the solution you provided below is that I used the "Replace a specific string" feature. I will be able to use this to update several different criteria in the FROM statement to query as needed.