Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Batch Macro + Paramaterize SQL Statement?

Philip_Burrow
5 - Atom

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!

 

12 REPLIES 12
AmeliaG
Alteryx
Alteryx

Hi @Philip_Burrow,

 

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:

batchmacroex.png

 

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!

 

Amelia

mshanton
6 - Meteoroid

Hello AmeliaG,

 

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?

 

thanks

 

Mike

AmeliaG
Alteryx
Alteryx

Hi @mshanton,

 

Did you make sure to only replace the specific string in the configuration of the Action tool and not the entire query?

 

Amelia

mshanton
6 - Meteoroid

Hello Amelia,

Looking at the XML in notepad
I found the difference between our two files...

Under the RuntimeProperties....

Your Macro had this...
"<Destination>1/File</Destination>"

While mine had this...
"<Destination>1</Destination>"

When I changed my code in Notepad to match yours it ran fine....

Do you know where you set that option within Alteryx so I won't have to edit the XML each time?

Thanks

Mike

AmeliaG
Alteryx
Alteryx

@mshanton,

 

That setting is also in the Action tool. If you look at my screenshot in an earlier post, you will see I've selected the file path as the destination. It is highlighted in the configuration window. 

 

Best wishes,

 

Amelia

mshanton
6 - Meteoroid

Hey @Amelia

 

It looks like you have a later version of Alteryx....  My company is currently on 11.7.4.37815

 

I apparently don't have that option... Under the Action Tool yet.. 

 

Capture.JPG

 

Thanks for your help...

 

Mike

AmeliaG
Alteryx
Alteryx

@mshanton,

 

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. 

 

Amelia

mshanton
6 - Meteoroid

Hey @AmeliaG

 

Yup, did that thanks... and I'm selecting only the word I want to replace...

 

I'm wondering if this is what it is..   I don't seem to have this option in my Alteryx Version...  I'm assuming this is a Destination Option. (See hightlight on this screen capture from your above post)

 

Capture1.JPG

 

Thx

 

Mike

AmeliaG
Alteryx
Alteryx

Hi @mshanton,

 

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. 

 

Best wishes,

 

Amelia

Labels