I use inner join by 1=1 to your original data set as below
And come up with something like this, if this is what you expected, please find attached workbook.
Hope this helps
Tableau_sample_v10.2.twbx 20.5 KB
I didn't get you exactly. But i feel you need to pivot the data for primary source. You can use Tableau functionality of pivoting data(you can select all friends columns in data source pane and right click and say pivot). Your data will look like this:
NAME , Pivot name , pivot value
David,friend 1, Tom
David,friend 2 , Dicg
David,friend 3, Harry
David,friend 4 , Bob
then you can join this with your second source on name or Pivot value or may be 2 joins on both the columns with the same table twice separately as per your requirement.
No, the question is not how to pivot data or not. Unless, that is a requirement of the solution. But whether this question is posed with the data being in rows or columns - it is still the same issue.
Thanks but that is not the question. Maybe I was not clear enough. The question is not whether some manual series of IF THEN statements can be written to address this example. I just presented a very simple example to make it easy to understand. But the target data set would be many thousands of key strings so any solution has to be entirely generated through functions.
Essentially, the target is to be able to produce a calculated field outputting a measure such as this:
Function: Wealth of <NAME> - Wealth of <FRIEND3>
The result then would mean that you could filter by Name and get the difference between two Names but the Names which are in the calculation change according to the value in the dimension.
It is important to maintain the specified hierarchy in the answer. So you could have (for example) the ratio of FRIEND1 to FRIEND4 as a measure.
The challenge is that you are selecting the 'Friend Names' on the basis of filtering the Dimensions for a Name. But then the calculated measure is derived from filtering on the outputs from those Dimensions.
This can be done very easily outside of Tableau but it is will create unnecessary amounts of extra data if it can be done within Tableau.