Determine If City Is Present Of Country Or Not?

[Kavya132017]

I have 2 columns, City & Country. I want an extra column of Flag Yes/No based on location of city in the country or not.

example:

City                             Country                          Flag 

Mumbai                         India                             Yes

Amsterdam                Australia                           No

Toronto                        USA                                No

New Jersey                 USA                               Yes

Any help is appreciated.Thank you

[Yoshiro_Fujimori · Accepted solution]

Hi @Kavya132017 ,

I think you need a table of Country / City pairs you refer to.

Once you have such a table, you can simply JOIN to know if each Country/City pair is on the table or not.

You can look for such database from somewhere on Internet, but I guess you cannot solely rely on the database out there.

For example, suppose there is a small town named "Amsterdam" in Australia.

You may want to flag as "Yes" or you want to ignore such a small town.

I think it depends on your preference and the preference should be reflected to your reference table.

[Kavya132017]

Thank you @Yoshiro_Fujimori  . That's what I'll need to do I guess, creating an exhaustive list.

[Kavya132017]

Hey @Yoshiro_Fujimori , I have another query. I have multiple excel files with same name format 

Data View 9-5-2023 10-15-48 AM (1), Data View 6-10-2023 10-15-48 AM (1).

I want to extract just the date & time stamp from these names. How can I do that?