Same url-different text

[diegomarino]

Hi, in linkedin data i can download connections and messages, but sometimes urls are written in a different way even if they go to the same page.



i think i can post that cause it is a public linkedin profile.

first link

https://www.linkedin.com/in/%F0%9F%8F%B3%EF%B8%8F%E2%80%8D%F0%9F%8C%88-7150b054

second link

https://www.linkedin.com/in/??????????-7150b054

they go, as said, to the same page, but i'm not able to Join the link cause they are different as text.
Is there something i can do?
thx


PS i deleted some part of links for privacy, so don't try to open, i assure you that point to the same page

[Gicu]

Use some string manipulation functions to isolate what seems like the unique ID 710b054 and use that in your join. I'd start with something like Mid(sURL,Instr(sURL,"-")).

Cheers,

[Edgar]

They might go to the same page but they likely perform a different action. Often times they look different based on the user, other times because they come from a reference site, or section of a site, others because they carry some data to be used later, etc., there are infinite needs/variations. The first link you posted is URL encoded, if I decode it, it shows a flag emoji. The second is just a bunch of question marks, also valid for a URL.

Post some actual links so we can figure out a good approach. If the forum has some restriction about the URLs then just post the string after the base url like
linkedin.com/in/post-this-part-123456

There is a trick I like to use when I want to find a clean URL to something, I just open a private browser tab, then I inspect the URL to see if there's some indication that the URL is carrying extra data, like
linkedin.com/in/post-this-part-123456?=extradata123123123
I remove the parts that look like functionality (in bold) and leave the rest, if it reaches the page, then I use that. I do that a lot with Google URLs and I do it from a private tab because I won't be logged in, therefore it won't carry my user/usage data.


As for joining these, I don't understand, what are you joining?

[diegomarino]

ok thanks
THe data are in two different tables, connections and messages. So i join the url in connections with the url in messages, and i can get who sent me the message

this is the link in the "connection" table

https://www.linkedin.com/in/%F0%9F%87%AE%F0%9F%87%B9-ester-compagnone%F0%9F%8F%B3%EF%B8%8F%E2%80%8D%F0%9F%8C%88-7150b054

and this is the link of the same person in the "messages" table

https://www.linkedin.com/in/????-ester-compagnone??????-7150b054

the second links has some replacement "?" so this is the pic



as i said is a public profile, so i think there are no problems

[diegomarino]

then, i saw that there are funcionts like "hyperlinkpart" but i cannot make it run

[Gicu]

URL_ID: Mid([URL],InStrRev([URL],"-")+1) would give you the unique ID:

Cheers,

[Edgar]

I'm having difficulties understanding what you're trying to join and why. Gicu has provided a way to leave just the apparent Id, but if I use it like this:
https://www.linkedin.com/in/7150b054
It returns a 404 page, not found, so I'm still not quite getting why you'd want to store such a link and later use it if it's not going to work. I understand that you have two links one from a table, another from the other table, what I don't understand is why you want to join the links. What do you mean by joining the links? what do you hope to achieve by joining the links? Should you not be using just the one that works?

[Gicu]

@Edgar: my understanding is that he has a table of persons (connections) and a table of communications from those persons (messages), so the purpose is to attach the messages to the right person. So my thinking was to build two queries based on those tables and add in each the calculated URL_ID field and use those in the form\subform setup or joined in queries as needed.

Cheers,

[Edgar]

@Edgar: my understanding is that he has a table of persons (connections) and a table of communications from those persons (messages), so the purpose is to attach the messages to the right person. So my thinking was to build two queries based on those tables and add in each the calculated URL_ID field and use those in the form\subform setup or joined in queries as needed.

Cheers,

Oh, I get it now. Thanks, Gicu.

[diegomarino]

Yes gicu (and Caro too) got my needs.
i'll try to extract the unique part. I just give a check (linkedin data are very messy) but i think this should be ok, i looked at some links and i think this method should work

[diegomarino]

unfortunely it's not a good way to join records, i have a lot of different results cause there are many kind of url.
I will join them by name, surname and date of connectios, it's the only way

thanks

[diegomarino]

well. i got the problem.
THe url in connections table is not decodified, it's Unicode but it do not shows the right character, they are not converted in unicode
the url in messages are converted in unicode, so the two urls do not match, but they are the same.

i tried "StrConv" but it do not convert the strange character in well-mannered unicode.

but "raw" urls not match, so i have to find a real way to extract unique url

[Edgar]

Post a representative sample of multiple url kinds so we can figure out a good approach for the extraction. If you can post a sample db, better.

[diegomarino]

Post a representative sample of multiple url kinds so we can figure out a good approach for the extraction. If you can post a sample db, better.

this is the db
https://hhfinanza-my.sharepoint.com/...NN6cA?e=no6tQ7

there are 4 tables
Connection1: the people i have in my first Linkedin account
Messages1: the exchange of emails between me and people i have in my first account
Connections2: the people i have in my second Linkedin account
Messages2: the exchange of emails between me and people i have in my second account

My need is to join connections1 to message1 (and the same for the other two tables), but the main problem, as you can see in posts above, is that url are written in a different way in connections and messages, even if they point to the same link.

i had no time to work on it, but i was thinking to create a unique key using part of the url and the name and surname, that are the same in connections and messages (no problems of different characters)

As far as i saw, if there is only one person with that name, linkedin do not add a code after the name-surname at the end of the url, if so there is some "-" with a code. Consider that the name and surname in url, if they have some "à" or "ò" etc etc are not displayed as in the field first name and last name (or in "to" field in messages tables).

i think i should make a function that recognize if there is a code after the name in the url, if not i can use the name-surname as ID, if so i have to make a key using name & surname & code at the end of the url.

[Gicu]

Hi Diego,
Please review this updated file and let me know if that is what you're trying to achieve. Please note that I had to remove the Connections2 and Messages2 tables and some records from the 1 table to be able to zip and upload here (file limit is 2 MB).
Cheers,