Table Calculation - How to extract a part of string based on a character, for ex '_'

Hi All,

I have a dimension in my dataset which has values as
family_rmkt_Outb_PTW
Lookalike_Comp_Static_PTPromo_Night etc.

I want to build two new dimensions using Table Calculation or custom field as I do not have access to LookML, so that I can add as two new columns to my table chart i.e.,

  1. Audience -anything before first ‘_’
  2. Creative -anything after the last ‘_’

Example1,
Field1
family_rmkt_Outb_PTW
Audience (Table calculation)
family
Creative (Table calculation)
PTW

Example2,
Field1
Lookalike_Comp_Static_PTPromo_Night
Audience (Table calculation)
Lookalike
Creative (Table calculation)
Night

How can I achieve something like in the below screenshot?
Content - already present in Looker
Audience & Creative fields need to be created based upon the value of Content field.

0 3 11.3K
3 REPLIES 3

mprogano
Participant III

I’d suspect that you can use position() to find the index and substring() to extract

@mprogano Thanks for this, I just used position("_",${pt_media_platform_content_data.content_test}) to whether it gives the position but it just returns value as 0 in the new table calculation created

On the other note, if this works, how can one differentiate between first underscore and last underscore using the function?

mprogano
Participant III

@Shafeeq_Rahaman you have it backward – first, it’s the string and second is the search parameter.
so you’d want to do position(${pt_media_platform_content_data.content_test}, "_") and you’ll get the first result.

Then you need to use substring to start at 0 and go to 1 less than the first position (e.g. position(${...},"_")-1). Once you have that you can look for the second one, by starting at one more than the position you started at and going to 9999999 (or something). If you loop until you get to the end.

Here’s some code to get you started:
(Assuming it doesn’t start with “_”, if it did just change the 0 to a 1)
This will get you the first word…

substring(
  ${pt_media_platform_content_data.content_test},
  0,
  position(${pt_media_platform_content_data.content_test},"_")-1
)

And in order to get everything after the 1st express, do the following.

substring(
  ${pt_media_platform_content_data.content_test},
  position(${pt_media_platform_content_data.content_test},"_")+1,
  99999
)

Now that you can get a single word and everything after you can do it as many times as you need to get everything you want. Don’t forget there’s a contain() function if you want to do if statements

Further Reading:

Top Labels in this Space
Top Solution Authors