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 nameUnique IDCustomer A123Customer B234Customer C345 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 Thank you for the batch macro solution. Addition to it, I have a scenario for the same data. I have Columns A, B, C, D, E F, H, I Columns A, B, C, F, H, I are Static Columns D, E are dynamic which might have account numbers appended dynamically. I need my dynamic columns between the static columns in the order I desire. However, when my workflow runs for each client, my dynamic columns are not in proper order as shown in below examples. I have given my current and expected outputs below. Client 1: Current order of Output: Column AColumn BColumn CColumn E Column FColumn HColumn IColumn D Account1Column D Account 2Column D Account 3 Expected order of Output: Column AColumn BColumn CColumn D Account1Column D Account 2Column D Account 3Column EColumn FColumn HColumn I Client 2: Current order of Output: Column AColumn BColumn CColumn FColumn HColumn IColumn D Account 4Column D Account 5Column E Account 6 Expected order of Output: Column AColumn BColumn CColumn D Account 4Column D Account 5Column E Account 6Column FColumn HColumn I Client 3: Current order of Output: Column AColumn BColumn CColumn E Account 10Column E Account 9Column E Account 8Column D Account 7Column FColumn HColumn I Expected order of Output: Column AColumn BColumn CColumn D Account 7Column E Account 8Column E Acount 9Column E Account 10Column FColumn HColumn I Thanks in advance!

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!!

Developer

Developer Tools

Preparation

Accepted answers

Qiu

@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.

1027-poornimak.yxzp

Qiu

@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.

1027-poornimak-r1.yxzp

poornimak

@Qiu Thank you for the batch macro solution. Addition to it, I have a scenario for the same data.

I have Columns A, B, C, D, E F, H, I
Columns A, B, C, F, H, I are Static
Columns D, E are dynamic which might have account numbers appended dynamically.
I need my dynamic columns between the static columns in the order I desire. However, when my workflow runs for each client, my dynamic columns are not in proper order as shown in below examples.
I have given my current and expected outputs below.

Client 1:

Current order of Output:

Column A	Column B	Column C	Column E	Column F	Column H	Column I	Column D Account1	Column D Account 2	Column D Account 3

Expected order of Output:

Column A	Column B	Column C	Column D Account1	Column D Account 2	Column D Account 3	Column E	Column F	Column H	Column I

Client 2:

Current order of Output:

Column A	Column B	Column C	Column F	Column H	Column I	Column D Account 4	Column D Account 5	Column E Account 6

Expected order of Output:

Column A	Column B	Column C	Column D Account 4	Column D Account 5	Column E Account 6	Column F	Column H	Column I

Client 3:

Current order of Output:

Column A	Column B	Column C	Column E Account 10	Column E Account 9	Column E Account 8	Column D Account 7	Column F	Column H	Column I

Expected order of Output:

Column A	Column B	Column C	Column D Account 7	Column E Account 8	Column E Acount 9	Column E Account 10	Column F	Column H	Column I

Thanks in advance!

Qiu

@poornimak
I will prepare a predefined Column Names in required order then do some magic 😁

1109-poornimak-r1.yxzp

All comments

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

@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.

1027-poornimak.yxzp

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

@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.

1027-poornimak-r1.yxzp

poornimak

@Qiu Thank you for the batch macro solution. Addition to it, I have a scenario for the same data.

Client 1:

Current order of Output:

Column A	Column B	Column C	Column E	Column F	Column H	Column I	Column D Account1	Column D Account 2	Column D Account 3

Expected order of Output:

Column A	Column B	Column C	Column D Account1	Column D Account 2	Column D Account 3	Column E	Column F	Column H	Column I

Client 2:

Current order of Output:

Column A	Column B	Column C	Column F	Column H	Column I	Column D Account 4	Column D Account 5	Column E Account 6

Expected order of Output:

Column A	Column B	Column C	Column D Account 4	Column D Account 5	Column E Account 6	Column F	Column H	Column I

Client 3:

Current order of Output:

Column A	Column B	Column C	Column E Account 10	Column E Account 9	Column E Account 8	Column D Account 7	Column F	Column H	Column I

Expected order of Output:

Column A	Column B	Column C	Column D Account 7	Column E Account 8	Column E Acount 9	Column E Account 10	Column F	Column H	Column I

Thanks in advance!

Qiu

@poornimak
I will prepare a predefined Column Names in required order then do some magic 😁

1109-poornimak-r1.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703