Dear all,
I am struggling with a task, where I would like to ask for your help.
Let’s assume the following scenario: I created a list of places where I “have been” or “want to go”, but I don’t know for every place all information about country, region, or city. This file is my mapping file (like a ruleset). In detail I have 3 different columns showing information about the place (Continent, Country and City). In a 4th column I added information if I “have been” or “want to go” to this place.
Now what I want to do is the following:
I want to match my mapping file with a data set about places around the world to see where I have been, where I want to go and what is not included in my list.
So, I want to use each of the 3 columns as a filter on my mapping file and if it applies successfully then I will add the 4th column with my information about “have been” or “want to go” to a general list of places around the world.
Therefore, I have two approaches:
1: Static approach to manually create for each row of my file an individual filter and filter my dataset and then join it. It would work but is a lot of manual work and not dynamic.
2: Dynamic approach. Which is 100 % preferable as I want to add further places in the future and don’t want to extend my workflow manually for each new place in my mapping file. But here am I Struggling due to different reasons:
In my mapping file are empty values. E.g., if I have been in a certain region but not in a specific city, the city column would stay empty. It doesn´t make sense to add all city in this region as this would be way too much work.
Please see attached my files. I hope somebody can help me out with.
Best
