1 of 1 people found this helpful
This is one place where Tableau Prep doesn't have an easy solution yet. There are, however, possibilities for tackling this kind of problem:
- In some cases it is possible to unpivot the data (turn rows into columns). Based on the data you shared, I don't see this working well in your case.
- Depending on your data source:
- Excel, flat-file, and the like: Since it's a one-time thing and you don't need an automated repeatable process, you might consider manually adding a row number / index to your source data. Then you could do a left join on Row = Row + 1. With a bit of calculation logic, you could see if the client matched between the joined rows and then get the date difference.
- SQL Server, Oracle, and anything that supports table calculations: use the table calculations in a custom SQL
- Use a ranking work-around to get the "row number" in Tableau Prep and then solve with a left join like above
Ultimately, Tableau Prep needs functionality that handles your use case and similar use cases. I'm very hopeful it will in the near future. Please consider voting for this to lend your support where the developers can see how much it is needed.
Hope that helps!
Thanks, the source is excel. It's very easy to do most of this directly in excel, however in my case although I don't need automation I do need reproducability which means that every data step should be documented and auditable from the point of opening the data source onwards. I shall vote.