Using Multi Variable Loop action to be generate multiples files

Purpose : - Generate a filtered file for each salesman which is emailed to him/her.

Brainstorming
1. I am thinking using new loop action in package, I should be able to pass variable into a transformation where I could technically pass this onto a validation but how do I even run this transformation ?
 2. How to generate customised file name based on column name?

Input 1 - List of Salesman with UniqueSales Code
 

Input 2 - List of all sales done by them 
 

Output 1 - Expected - Filtered CSV containing relevant data for sales person 
 

All the CSVs are attached in the files (seriously allow us to upload csv)  
 

Any ideas?

Let assume that you have a sales file with 10 million rows and 10,000 customers lookup file

Loop reads list of customers file and passes customer id to the transformation

Transformation reads 10 million rows from sales file and filters it by customer id and creates the file

So the entire loop will read 10 million rows 10,000 times

That will take a lot of time and processing power

The better and faster option is to use a single transformation

The default repository has an example 0016 (every example has a number)

It does exactly what you want



 

Peter.Jonson wrote:

Let assume that you have a sales file with 10 million rows and 10,000 customers lookup file
Loop reads list of customers file and passes customer id to the transformation
Transformation reads 10 million rows from sales file and filters it by customer id and creates the file
So the entire loop will read 10 million rows 10,000 times
That will take a lot of time and processing power

• This is a bit of extreme example , my files are not more than 100,000 rows. Although I would love it if my sales team can generate 1million rows for a year
  
   
• But God Forbid I have a scenario that I have 10 million rows & 10,000 customer lookup files , I will absolutely prefer automation and tonnes of processing power to do this than load this 1 by 1 with 0016. Worst case I will do this at midnight via scheduler.
• But just for reference purpose , I dowloaded a 5m record file with 173 unique customers (cities). On OSX , in another program which I am sure is highly optimised takes around 1 min 40 sec  See boring countdown 
• 5 Million CSV file was downloaded from HERE

>>>I will absolutely prefer automation and tonnes of processing power to do this than load this 1 by 1 with 0016.

It is is probably all my fault because I am not explaining it correctly.

0016 is the fastest option, It does everything in one go, without loop  and not one by one
 

HI Peter,
Thank you for the last post , it's much clear now

• Can you explain the mechanics of this calculations below

• Can I also assume this will work for CSV to CSV output as well? This is csv to xls