This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Using IN-DB tools, I’m trying to create a process that queries the same database table multiple times, just swapping out a field for each run. Each time it runs, it appends the results to a second table.
I think I can accomplish this by parameterizing the SQL query (creating a parameter for the field, then swapping field), but I’m not sure how to do that IN-DB. I’ve looked into using Dynamic Input IN-DB but it doesn’t like any connection string I give it. I’ve also looked into using the transpose tool, but this particular data set is 8M records by 180 fields, so using transpose murdered the database.
Is there a way to parameterize a field name SQL query, then use a batch macro to run the SQL query x number of times, one for every parameter value?
Say this is my data structure for [testTable], where 1 means a member has visited a state, and 0 means they haven’t. I want to flip this from a wide to a tall table, keeping only the records where a member has visited a state.
Thanks for your question! This is possible. To achieve the desired results your workflow should have the following:
1. Input data tool connecting to your database with the SQL query mentioned above.
2. A 'Control Parameter' tool which you will connect to your 'Input Data' tool and an 'Action' tool will automatically appear.
3. In the 'Action' tool, check the box at the bottom of the configuration window to 'Replace a Specific string' and then remove everything but the state abbreviation. See configuration below:
4. Add a transpose tool after the Input data tool. Choose all fields to be key fields except the state field and the 'unknown' field. The unknown and the state field should be checked as the data fields.
5. Add a macro output tool after the transpose tool.
Once you've inserted the macro into your workflow, the input will be a unique list of the state abbreviation columns.
I've attached an example workflow. Let me know if you have any questions!
I used your solution for a similar project I am working on. I substituted my code into your batch macro example and it worked perfectly. However, I then opened up a new session of Alteryx and attempted to recreate your solution using the exact same tools, but that macro never works... even though it looks identical to yours. Did you adjust any other settings prior to building your macro?
The action tool is not different between our two versions. In your screenshot, I see you have highlighted the correct destination. At the bottom of the same configuration window you should see a check box option to 'Replace a specific string' - the string which you want to replace should be the piece that is changing in your query.
The option you've highlighted can be turned on in your 'User Settings' but will not affect the functionality of the tool - it simply allows you to see the underlying xml. If you send me your workflow, I can take a look.