To process a workflow for a given list of customer names individually

[poornimak]

I have a workflow with some data processing. I want the workflow to run for each customer from a list of customers in an excel file and generate output file for each customer.

Example:

Customer List:

Customer name	Unique ID
Customer A	123
Customer B	234
Customer C	345

I have a workflow a.yxmd

Expected output:

a.yxmd should run for each customer - Customer A, Customer B, Customer C and generate separate excel output with Customer name as the output file name. Please note that my workflow a.yxmd also contains customer data. Any help would be appreciated.

TIA!!

[Qiu]

@poornimak 
Try to search Batch Macro 😁

[poornimak]

Can you please share a workflow that suits my scenario?

[caltang]

You can use a Formula tool to write the output path of your Excel file + the Customer identifier. For example: 

“C:\Users\YourUsername\Desktop\FolderHere\TestOutput.xlsx|||” + [Customer Name]

From there, attach an Output tool and configure on the left. On the bottom left, choose change entire file path and select the Output path you created with the formula tool earlier. Be sure to untick the keep in output button. 

Then, be sure to also select the output method to create new sheet for your first run. Subsequent runs you can change it to overwrite existing file. That should do the trick for you.

[poornimak]

Above solution is feasible for generating my output file for each customer. 

However, I want the workflow to pick one ID at a time from a list and run.

For example:

Customer List(This list is in Excel):

Customer name	Unique ID
Customer A	123
Customer B	234
Customer C	345

From the above list, I need to pass Customer A and run my workflow a.yxmd and generate output for that customer only. This needs to iterate for all the customers in the customer list.

Customer A --> run a.yxmd --> generate output for Customer A

Customer B --> run a.yxmd --> generate output for Customer B

...

Please note that my workflow a.yxmd also contains customer data which needs to be mapped with external customer list that is mention in the above table.

Let me know if any information in required. It would be helpful if you share a workflow example/template that works for my requirement.

[Qiu]

@poornimak 
It will much better you can provide a set of sample input and output so we can be more clear also.

[poornimak]

@Qiu :

Please see below example:

If I run my workflow with all the customer list I get 

Customer Name	ID	Loan Type A_AccountNo1	Loan Type B_AccountNo3	Loan Type A_AccountNo2	Loan Type A_AccountNo4
Customer A	123	1234567(Amount)
Customer B	234		4567890(Amount)	234567.2345(Amount)	7689790(Amount)

instead, I want output to be exported to excel for each Customer.

Sample output 1:

Customer Name	ID	Loan Type A_AccountNo1
Customer A	123	1234567(Amount)

Sample output 2:

Customer Name	ID	Loan Type B_AccountNo3	Loan Type A_AccountNo2	Loan Type A_AccountNo4
Customer B	234	4567890(Amount)	234567.2345(Amount)	7689790(Amount)

[Qiu · Accepted solution]

@poornimak 
So my understanding is that you also remove the null columns when exporting the data for each customer.

We can use a batch macro and use a Cleansing tool to remove the null columns.

A quick sample is as below.

[poornimak]

@Qiu Thank you! This worked for me.

I have another requirement under the same data. I have a template to attain.  'Loan Type A_AccountNo1', Loan Type A_AccountNo2' ...

are dynamic columns. If my customer has any account under type 'Loan Type A' or 'Loan Type B' corresponding amount appearing under it.

Now, if my customer doesn't have any account under type 'Loan Type A' or 'Loan Type B', I want an empty column. Please see below example.

Sample Data:

Customer Name	ID	Loan Type A_AccountNo1	Loan Type B_AccountNo3	Loan Type A_AccountNo2	Loan Type A_AccountNo4
Customer A	123	1234567(Amount)
Customer B	234		4567890(Amount)	234567.2345(Amount)	7689790(Amount)

Output1:

Customer Name	ID	Loan Type A_AccountNo1	Loan Type B
Customer A	123	1234567(Amount)

Output2:

Customer Name	ID	Loan Type B_AccountNo3	Loan Type A_AccountNo2	Loan Type A_AccountNo4
Customer B	234	4567890(Amount)	234567.2345(Amount)	7689790(Amount)

[Qiu · Accepted solution]

@poornimak 
I assume you want all the Load Type to be present even some of them are having no value.

It turned out that I have to go through a lot step, reason 1 is to match the missing load type with a pre-defined list.

Reason 2 is that Space in your field name will be changed to "_" with Cross Tab tool, so I have to make some special treatment.
I hope this will work for you.