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

[migle-maciuleviciute]

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 solution]

@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

[jrsheppard]

You can use a batch macro and create a control parameter to do this record by record.

Alternatively, a simpler method is to modify your Select statement to include the "IN" clause.

https://www.w3schools.com/sql/sql_in.asp

SELECT field1, field2, field3, field4

FROM invoice_table

WHERE invoice_id IN (List_of_IDs);

You will need to concatenate the IDs with commas to one cell through some data preparation - I suggest simply using a summarize tool to concatenate (make sure the IDs are converted to a string first).  For the output tool, you can configure it to dynamically use the invoice ID column for a new worksheet.  There are several tutorials showing how to do that.

[migle-maciuleviciute]

Hello,

Thank you! 

Only issue right now - how to split the data according to unique invoice IDs and save them into separate excel files efficiently?

[Luke_C · Accepted solution]

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/893...