Hi Guys,
I am new to using Prep and would appreciate some guidance on how to do the following.
I have to calculate the number of days from when the business was created (Business Table) and when the first Event was created (Reports Table).
The result I am trying to achieve is shown in the Results Desired table in the below image.
Cheers,
Greg
Jan 9, 2019 8:20 PM
Something like this ?
Greg,
Here's an alternate approach that is very similar to Chris' but simplifies things a bit. In Chris' example, he joins the aggregate step back to the previous step (in effect, creating an LOD calc). I love that approach! But in this case, I think you can avoid the complexity and simply use an aggregate to find the minimum number of days to the first event for each individual. It assumes that the event date is after the created date (which, even in your data, it is not) - but a simple filter in the Clean step removes any cases where the event date comes first.
Now, if you need any of the detail from the records that is lost from the aggregation, then definitely use Chris' approach to join the aggregate back to the the previous step (that has the details)!
I've attached the packaged flow that is shown here:
Hope that helps!
Joshua
Hi Chris & Joshua, thank you for both your contributions. The world is a happy place now
I appreciate you taking the time to explain the solutions.
Jan 10, 2019 2:37 PM
Now the tough task You can mark ONE response as Correct, you can mark any/all response as being Helpful.
Both answers work. But I prefer the solution Chris put forward as it fits with where I am going the project.