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?
For example:
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.
Name | DOB | MN | TX | WA | CA | AZ |
MEMBER A | 1984-06-04 | 1 |
| 1 |
|
|
MEMBER B | 1988-05-01 |
| 1 |
|
| 1 |
MEMBER C | 1963-03-21 | 1 |
|
| 1 |
|
MEMBER D | 1965-06-04 | 1 | 1 |
|
| 1 |
MEMBER E | 1983-01-18 | 1 |
|
|
|
|
MEMBER F | 1982-12-19 |
| 1 | 1 | 1 |
|
MEMBER G | 1936-07-06 |
|
|
| 1 | 1 |
SELECT * from [testTable]
WHERE [MN] IS NOT NULL
Insert results into tableB
SELECT * from [testTable]
WHERE [TX] IS NOT NULL
Insert results into tableB
SELECT * from [testTable]
WHERE [WA] IS NOT NULL
Etc..
Til the final table looks like this
Member | DOB | State | Visit Flag |
MEMBER A | 1984-06-04 | MN | 1 |
MEMBER C | 1963-03-21 | MN | 1 |
MEMBER D | 1965-06-04 | MN | 1 |
MEMBER E | 1983-01-18 | MN | 1 |
MEMBER B | 1988-05-01 | TX | 1 |
MEMBER D | 1965-06-04 | TX | 1 |
MEMBER F | 1982-12-19 | TX | 1 |
MEMBER A | 1984-06-04 | WA | 1 |
MEMBER F | 1982-12-19 | WA | 1 |
MEMBER C | 1963-03-21 | CA | 1 |
MEMBER F | 1982-12-19 | CA | 1 |
MEMBER G | 1936-07-06 | CA | 1 |
MEMBER B | 1988-05-01 | AZ | 1 |
MEMBER D | 1965-06-04 | AZ | 1 |
MEMBER G | 1936-07-06 | AZ | 1 |
Any help would be appreciated!
Solved! Go to Solution.
Hey @AmeliaG
First of all, thanks so much for your help...
Here is a screen shot comparing our Action Tool XML... so you can see the difference...
I just quickly created a Macro, (attached) and after creating it.... it did not add the "/File" after my destination xml logic.
Thanks, Mike
Hi @AmeliaG @Philip_Burrow @mshanton
I am using alteryx v11.7.4.4 and i am also having the same problem. I had to edit the XML file and include "/File" into <Destination>1/File</Destination> to get the macro working. Anyone know why?
Hi @AmeliaG
I am trying to create a Macro in similar lines. Below is my situation, Kindly help:
1. There are multiple databases
2. But SQL query to extract data is same
So, I wanted to build a Drop-Down tool to select the desired database and then corresponding SQL would work and give the output. Kindly help in formulating the workflow.
Thanks!!