-
1. Re: Combine two rows into one and sum their values
John Sarantos Aug 13, 2019 1:55 PM (in response to Cassandra Sherrill)1 of 1 people found this helpfulHi Cassandra,
What you are looking for is a fuzzy matching function. Tableau Desktop doesn't have one, but there have been many different attempts at working around the problem.
Here's a post from the forum where a few suggestions are made: Fuzzy Duplicates in data (Vendor Names)
There's also a post here about an approach someone took with Tableau Prep: Can Tableau Prep do Fuzzy Matching? — OneNumber
Hope that helps!
-John
-
2. Re: Combine two rows into one and sum their values
Cassandra Sherrill Aug 13, 2019 4:07 PM (in response to John Sarantos)Thank you, John! I guess I should feel better that there's a reason why I couldn't figure out how to make it work, but it's always frustrating to smack up against a program limitation. I'll see if those links can help me. (Unfortunately, I don't have Tableau Prep.)
-
3. Re: Combine two rows into one and sum their values
Peter FakanAug 13, 2019 4:26 PM (in response to Cassandra Sherrill)
1 of 1 people found this helpfulHi Cassandra, I realise this does not work well at scale, but are you familiar with the group members function ? Combined with the Alias feature this is an efficient method of matching data quickly.
HTH
Peter
-
4. Re: Combine two rows into one and sum their values
John Sarantos Aug 13, 2019 4:42 PM (in response to Cassandra Sherrill)You're welcome and good luck!
-
5. Re: Combine two rows into one and sum their values
Deepak RaiAug 13, 2019 5:27 PM (in response to Cassandra Sherrill)
Hi Cassandra,
Since you say you don't have Prep, so let's do it in Tableau;
Here it is:
Use These Calculations:
New Name:
{FIXED REPLACE(IF LEN(Name)-Len(REPLACE([Name]," ",""))>1
THEN SPLIT(Name," ",2) Else SPLIT(Name," ",1)END,"'",""):MAX(IF {FIXED REPLACE(IF LEN(Name)-Len(REPLACE([Name]," ",""))>1
THEN SPLIT(Name," ",2) Else SPLIT(Name," ",1)END,"'",""):MAX(LEN(Name))}=LEN(Name)
Then Name End)}
New Address:
{FIXED [New Name]:MIN(IF {FIXED [New Name]:MIN(LEN(Address))}=LEN([Address]) THEN Address End)}
Using These you should get about 99-100% of your problem solved, so if you see some Comma or something else left in New Name, just tune my calculation accordingly, but it will work.
Thanks
Deepak
If it Helps, Pl Mark it Helpful and CORRECT to Close Thread
-
Combine example (1)_v2018.1.twbx 62.0 KB
-
-
6. Re: Combine two rows into one and sum their values
Cassandra Sherrill Aug 14, 2019 9:18 AM (in response to Peter Fakan)Thank you for reminding me about that! That's a great solution for smaller files for sure.
-
7. Re: Combine two rows into one and sum their values
Cassandra Sherrill Aug 14, 2019 9:38 AM (in response to Deepak Rai)Thank you, Deepak! Those are some new functions for me, so I'm going to have to go in and examine them for sure.
ETA: Wow, that's a lot of calculations to get there! I'm pretty sure I would NEVER have been able to think my way through that whole progression. I'm impressed.
-
8. Re: Combine two rows into one and sum their values
Deepak RaiAug 14, 2019 9:44 AM (in response to Cassandra Sherrill)
Yes, lot of calculations and once I thought to give up as my initial thinking was not in right direction, but then light bulb glow up and here is the result.
Thanks
Deepak