the lookup method worked.so i asked the clients to provide with a look up of reasons.but they stated that those reasons are entered by the customers(i.e sort of like a feedback note) and so they will not be able to provide a lookup as such.
a customer may express any opinion.I've told them its an ETL process we are looking at but they want this to be done through tableau
If technique #5 isn't an option then you could use technique #4 in The Cross Join Collection.
SELECT d.[Reasons] , l.[Pos] , mid$(d.[Reasons], l.Pos, instr(l.Pos, d.[Reasons] + '~', '~') - l.Pos) as [Reason] FROM [data$] d, [lookup$] l WHERE l.Pos <= len(d.[Reasons]) AND mid$('~' + d.[Reasons], l.Pos, 1) = '~'
I didn't mention technique #4, because it might too slow. But if your dataset is small, you could use it and save the result as an extract so the consumer experience of the workbook is good (fast).
Another option is to wait until Tableau 9 is released (or in Beta if that is OK). See more about the split functions in L ive Str eam of Keynote | Tableau Conference 2014. There are two functions: horizontal split and vertical split. It is the vertical split (1:10:59 to 1:12:04) we need for this task.