Removing GUID from Columns.

cjsmith718

Hey all.

Hoping I could get some help with this one. I am fairly new to DOMO but I have done some research before I posted this.

I am looking for the best way to take data in a column and convert it to something else. Problem is that it does very from time to time. I have some examples of the existing data and what I would like it to look like after.

Example 1:

Existing text: SharePoint Group: SharingLinks.2e164b0a-d670-449f-b731-5da0a05853a4.Flexible.c033bacb-a4dd-464d-bedf-843ad35ef29d

To become: SharePoint Group

Example 2:

Existing text: O365 Linked Group: 009f6476-1726-4cb9-98dd-8ec4fb00e8b8

To become: O365 Linked Group

There may be more questions once I get this part working

Thank you.

GrantSmith

Hi @cjsmith718

It appears you're wanting the first part of the text before your colon. If that's the case you can use a Split Column tile in a magic ETL dataflow to get the first part of your string before the colon.

MarkSnodgrass

It looks as though you want anything before the colon to be your new field. You can use the SPLIT_PART function to extract this:

split_part(`String`,':',1)

This will take everything before the colon and ignore everything else.

cjsmith718

Thank you.

Some of the cells in this column had text like AD Group: All Users

In this case, I didn't want to remove the text after the colon but I don't think that we have much of a choice without possibly using BeastMode. Just to clarify:

Example 1:

Existing text: SharePoint Group: SharingLinks.2e164b0a-d670-449f-b731-5da0a05853a4.Flexible.c033bacb-a4dd-464d-bedf-843ad35ef29d

To become: SharePoint Group

Example 2:

Existing text: O365 Linked Group: 009f6476-1726-4cb9-98dd-8ec4fb00e8b8

To become: O365 Linked Group

Example 3:

Existing text: AD Group: All Users

To stay the same

Thanks again!

MarkSnodgrass

Depending on how many exceptions you have, it's easy to just add a case statement into the process so it would leave it alone if the group was AD Group: All Users.

Something like this:

CASE WHEN `String` = 'AD Group: All Users' THEN `String`
ELSE split_part(`String`,'-',1)
END

You could add additional when statements if you have other exceptions. You can do all of this in a formula tile in Magic ETL, or in a Beast Mode in Analyzer.

GrantSmith

In that case regular expressions and a formula tile are your friend.

Put the following code into a formula tile in the New Magic ETL to strip out anything after the colon if the text after the colon contains a GUID: (replace text with your field)

REGEXP_REPLACE(`text`, '^([^:]+): [^ ]*[a-g0-9]{8}-[a-g0-9]{4}-[a-g0-9]{4}-[a-g0-9]{4}-[a-z0-9]{12}.*','$1')

To break this down:

^([^:]+):

This says get me 1 or more (+ ) the non-colon [^;] characters at the start (^ ) of the string and store them into a match group ()

Following the colon-space delimeter:

[^ ]*

Tells it to match 0 or more (* ) non-space characters [^ ]

 [a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}

Matches the format of a GUID 8/4/4/4/12 numbers(0-9) and letters (a-g)

.*$

Says match everything else until the end of the string.

The way regular expressions work is if the string doesn't match the expression it'll just return back the string. So in your case of the All Users example it won't affect it at all.

Finally $1 tells the REGEX_REPLACE function what to replace the text it found with the first match group (in this case everything before the colon - what we defined above with the parenthesis)