4 Replies Latest reply on Feb 28, 2017 4:55 AM by shashank.sastry

Joining id of table 1 to other id(which is comma seperated value) of table 2

shashank.sastry Feb 28, 2017 1:12 AM

I have a requirement which is like this

table 1

ID name

1 john

2 sam

3 nick

table 2

ID PRODUCTS PURCHASED

1,2 IPHONE

1,2,3 IPAD

3,2 MACBOOK

Any customer can have multiple product purchases.

I can't join these tables as ID of table has comma separated values data,so is there any way I can convert ID of table 2 into normal rows with single ID value and then join that with table 1 to get customer info.Can this be done from tableau side instead of DB?

1. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

Gourav Sharma Feb 28, 2017 3:00 AM (in response to shashank.sastry)

Hi Shashank,

Try to format the data for table 2 like below:

ID          PRODUCTS PURCHASED
1                 IPHONE
2                 IPHONE
1                 IPAD
2                 IPAD
3                 IPAD
3                MACBOOK
2                MACBOOK

Then use data blending to get the results.

Thanks,
Gourav
Like Show 0 Likes(0)

Actions
2. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

shashank.sastry Feb 28, 2017 3:16 AM (in response to Gourav Sharma)

Hi,

Yes for 2-3 rows its fine if you have millions rows coming to tableau its not the feasible option.
Like Show 0 Likes(0)

Actions
3. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

Mahfooj Khan Feb 28, 2017 3:20 AM (in response to shashank.sastry)

Hi Shashank,

If its a one time activity then I can show you the steps through which you can transpose the data source 2. If its a long term activity then I would recommend try to transpose your data source 2 at database level.

For one time activity, first connect your DS (data source) 2 in tableau and apply custom split on your ID field like this
Once done you'll get this view
Now copy the above data and paste in excel. Delete the First Column (ID). Now connect below data in tableau in a new workbook.
Now select all the ID fields and right click and pivot
Your data will get transpose. Delete the null columns. Put data source filter to hide null values.
Now add your DS 1
Finally you can drag your field like that. Filter the nulls from ID column.
Hope this help.

Mahfooj
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

shashank.sastry Feb 28, 2017 4:55 AM (in response to Mahfooj Khan)

Yeah,
thanks for this approach,

I did lot of methods in which even i did split and pivoting that was the first idea while encountering this problem but again if it involves millions of date this method works lot of again manual work
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

Joining id of table 1 to other id(which is comma seperated value) of table 2

1. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

2. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

3. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

4. Re: Joining id of table 1 to other id(which is comma seperated value) of table 2

Sign In or
Create your Account

Actions

More Like This

Sign In or Create your Account

Sign In or
Create your Account