Removing GUID from Columns.
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.
Answers
-
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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!
0 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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)**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive