Hello all -
New Alteryx user here, so sorry if this is has been asked already.
I am working on a program that will be run every year. One of the jobs requires about 10 data pulls from different tables on 1 SQL server every year. So every year, these 10 tables will be different, and so I am looking for a way to do an input data tool on a dynamic table name.
Ideally, these tables would be defined as constants, or there would be a prompt asking the user to input the names of the tables. I want to avoid having the user open an input tool and manually map to the new table.
can anyone offer any help on this?
Thanks much
Solucionado! Ir para Solução.
This is exactly the sort of scenario the Dynamic Input tool was designed for.
https://help.alteryx.com/2018.2/DynamicInput.htm
In your workflow, prepare the table name as a variable. Drop a Dynamic Input tool on the canvas and use the "Edit..." button and configure the query exactly as you would in a regular Input tool. Once that's done, go back to the Dynamic Input configuration and select the "Modify SQL Query" button and "Add" a "Replace a Specific String" option. Then edit the "Text to replace" so it shows the table current referenced in the query and select your table name variable to replace it with.
As for how you design the table name input, that's up to you if you'd like to define formula or user inputs to update those values.
Charlie - thank you very much for your reply. I thought that that was the tool to use, but I didn't understand why you have a normal input tool feed into the dynamic input tool if you have to re-enter the query anyways in the dynamic input tool. I also am having a bit of trouble with syntax still, but I just needed to know that I was using the right tool. I will read up more about this specific tool. thank you for your help.
wait, im sorry, but what tool do you have feeding into the Dynamic input tool?
wait sorry again. I think I get it now. so you use an input text tool to call the constant with, and then feed this text into the dynamic input tool. i tried something like this, but had the wrong syntax. it works now seemingly. thank you so much Charlie
Happy to help! The table input does't have to be a file input, it could be a Text Input, or even use other tools to determine the table automatically.
Let's say you wanted to update the table to the most recent table that begins with the prefix "SALES_".
- Use an Input tool to query "SELECT name, modify_date FROM sys.tables with (nolock)"
- Then a filter tool where left([name],6)=="SALES_"
- Sort by Modify_date Descending and Sample the first record.
- Use that table name field as the input to your Dynamic Input tool and every time the workflow is run, it will find the latest table and use that in the query.