This kind of structure can be solved using Tableau Prep. However, please take a moment to vote up this idea as it would take the complex solutions I'm going to share and reduce it to a few simple steps: https://community.tableau.com/ideas/8732
It's a little easier to solve if we work under the assumption that the Purchase ID increments. Here's a solution in that case :
The crux of the solution is the join shown above which joins every record to every other record where the User IDs match and the Survey ID on the left is <= the Survey ID on the right. That means that for every user, we get a record for every survey matched with itself all surveys that come afterwards. The next step is to aggregate per user and survey to find the MIN matched Purchase ID. That might have come from the survey itself or a subsequent one. Again, that depends on the assumption that the purchase ID is incrementing as we take the minimum.
If that's not true, the solution gets a bit more complex:
You'll notice the original join along with some logic to find the matched survey ID's that have purchase IDs and then some joins to use that to get to the purchase IDs that those survey IDs had. There's more than I can write-up easily here, but I've attached both the simpler and more complex solutions. Feel free to ask any questions for anything that doesn't make sense.
And again, please add your support for this feature, which would turn the complex solutions shared above into a simple step or two: https://community.tableau.com/ideas/8732
Hope that helps!
That's definitely true. I like inserting Clean steps along the way to see the data and take action if needed. I also think it is more clear to do a self-join on two steps of the same kind, but you are absolutely correct that those steps are not needed.
Well, it works perfectly !
Thanks both of you for you answers, your solution helps me quite a lot.
I also support a better solution since both workarounds here do not work in the case where columns have a text data type instead of numerical values.