Replace specific string in a query and save data as an Excel file (loop)

Question

Dear all,

I have been having a problem and cannot find the solution so far:

I am connecting my input file to a DW that has a very complicated query, therefore, in order to get the detailed invoice information, I need to modify the query each time and denote the Invoice_ID. I would like to have a loop that would:

1. Take single invoice ID from a text input file (total of 71 invoice IDs);

2. Insert a single invoice ID to the query by replacing a specific string ('INVOICE_ID');

3. Retrieve the dataset and save into an excel file, rename the file based on a specific field;

4. Repeat until all invoice IDs are inserted and respective information saved in separate excel files.

I understand that probably the answer is an interactive macro, however, having difficulties in building one. Any help would be much appreciated!

MarqueeCrew · Accepted Answer

@migle-maciuleviciute ,

I think you're really just looking for a Dynamic Input tool.  You can substitute the "dummy" invoice number in a SQL statement for the incoming list of invoice IDs and run your queries.  The output likely has a "INVOICE" data field from which you can use an OUTPUT tool and take the file name from incoming formula tool.  You'll want to use the format of Invoice999999.xlsx|||Data or something clever.  Who knows, you might want to put each section into a separate tab and make it something like "InvoiceData.xlsx|||" + [Invoice] for a formula.

Cheers,

Mark

Luke_C · Accepted Answer

Hi @migle-maciuleviciute

Take a look at the below thread. To output to separate excel files you'll need to add a field with the full file path (including tab name), then in the output tool select change entire file path and choose the field that has your file path

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Output-to-multiple-xlsx-files/td-p/89339