Help: Iterative Switch/Case Statement

[Kyle15]

Hi All,

i'm working on a project to properly attribute website results:

I have website URLs that I'm trying to break out and categorize.  The URL composition seems to use the character "-" as the delimeter.

The URLs do not follow an exact format, meaning, the third part of one URL could be the region indicator and it could be the 2nd,4th, 5th, etc. part in another URL. visually abcd-efg-<region indicator>     vs wxyz-<region indicator>-lsod

I have 2 tables

one that has the full URL

Another that has the region categorization(about 25 records), looking something like this:

code   retion to return

eu      europe

us      united states

ch      china

I'm trying to avoid writing a long if or switch statement that can function accounting for the variability of the makeup of the URL string; I'm thinking the contains formula.
I want to be able to look for eu, us, or ch in the website URL, if found, return europe, united states, or china for every URL in the first table.

I could be overthinking this, but I couldn't think of a simple solution based upon my skills so far.

[patrick_digan]

@Kyle15 Can you provide a sample record or 2 in its entirety? It can be dummy data. I would see if the find replace tool accomplishes what you're looking for. It has the ability to find and replace(or append if that's easier in your case).

[Kyle15]

OSMAM-6C-AMER-EN-HPH-AIX-RBFPromo

osmam-rs-amer-en-hmm-i-loy-you

It just so happens that in both of these, the region indicator would be in the 2 part of the URL; 6C and rs in the URLs respectively.

[patrick_digan · Accepted solution]

@Kyle15 I would use the find replace. Here is a picture of the attached v11.0 workflow. You would just have to maintain that lookup table. I've been in your shoes before and I've found that a lookup table with the find replace is cleaner and easier to maintain than any sort of formula approach. Let me know if the result isn't what you were looking for!

[Kyle15]

Patrick,

Your solution is spot on and working like a charm!

Thanks for your help!