Dynamic filter with mapping file with multiple columns

[Joma]

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

[MattBSlalom · Accepted solution]

My understanding is that you want to lookup the "mapping" data against the "country region continent" data based on any of the shared fields (continent, country, or city) with those fields being sparsely populated.  Since the data provided is manually created (in the mapping file at least), there are some typos such as "Nord America" instead of "North America".  With that in mind, some of the matching is not ideal given the potential data entry errors.

With that said, there are 2 possible versions in the attached workflow.  In the first case, we try to match specifically on populated columns.  So, if Continent, Country, AND City are all populated, that row will only try to find a match with all fields in use.

The 2nd version starts as specific as possible (all 3 fields matching), but continues to try all other possible combinations progressively getting more generic matching criteria (ending with just matching on Continent).

Hope this helps.  Let me know if I've misunderstood any of the request.

[Felipe_Ribeir0 · Accepted solution]

Hi @Joma 

If i understood your requirements well, here a sample of the expected output and the workflow.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[Joma]

Thank you both! That helped a lot!