the pattern for using iterations in general is to split a large dataset into small chunks, then process one chunk in one iteration, then append processed chunks back into a result table. When arranged right, iterations should not append the entire dataset multiple times.
The pattern for using Iterate Table is to pass entire table into the iterated table and then filter it using a parameter (assigned from the calling project) in order to obtain a chunk to process.
In your case:
1. The source table for iterations is Purchases.
2. Each chunk is defined by ReturnKey.
3. The task is to take the total return for a chunk and distribute it cumulatively across purchases.
Here is how the logic works:
1. Calculate aggregate returns for each ReturnKey.
2. Filter out returns to keep only purchases.
3. Iterate: for each ReturnKey distribute aggregated return across purchases for this ReturnKey. To speed up things we use Iterate Table to pass table Purchases in memory. ReturnKey and total return amount are passed as parameters.
In the iterated project:
1. In each iteration table Purchases is filtered using ReturnKey (which is different on each iteration) in order to keep only purchases for that ReturnKey.
2. Use Running Total and Rule transformations to distribute the total return amount for this particular ReturnKey.
In this case no reading/writing is performed in each iteration -- everything is calculated in memory so performance should be good.
Result: returns test2.zip (74.5 KB)
- In your example you created returns.csv in one derived table, but it was used in iterations in another derived table independent from the first one. EasyMorph calculates independent tables in parallel (asynchronously) when possible. It means that iterations could start earlier than returns.csv is ready which would cause an error. To deal with such cases there is the Synchronize transformation. See this blog post for more details: Using synchronize transformation.
- I suspect iterations might be not necessary at all in this case because the Running Total transformation supports grouping. But I didn't try it.
- In my variant I assumed that all ReturnKeys had a return, which might be not the case in the real-life scenario.