1 of 1 people found this helpful
Here are some ideas to get you started:
In general in Prep this kind of algorithm is typically implemented one or more aggregations and/or calculations (potentially with some filtering as well) that are then joined back to the “main” data flow. So the major Steps you’ll use are Aggregate and Join steps, potentially with some Clean steps.
SUMIF() can be done in Prep in one or two steps.
The one step operation is to use an Aggregate step where the aggregation is using the necessary grouping. If the IF condition is complex then you might need to create a calculated field or do filtering before the Aggregate step.
“Carrying” or “bringing forward” a value in Prep is more complicated at this time. You’ll need at least two steps - a calculated field in a step to create the necessary offset on the date and then a Join step to join the prior to the current. For example if you are doing a one week offset then you’d add a week to the date - I usually call this new calculated field something like “Date for Join” and then join on the other key field(s) and then Date for Join = Date. Also note in the Join you’ll have to pay attention to your desired business logic and left/inner/right semantics if your data is sparse (and potentially do some date padding).
If you need more assistance then I suggest you post some sample data in a packaged flow and your work so far and let us know where you’re needing help.