Correct Usage for REGEXP_EXTRACT in Google Data Studio to Create A Calculated Field - Keep Getting 'Null'
I am writing reports in Google's Data Studio. I have successfully created several custom dimensions in the past using REGEXP_MATCH on the 'Keyword' dimension combined with CASE statements to create the dimensions I need. This one has me stumped.
I have data coming in through the 'Keyword' dimension that contains a substring that I would like to extract and display as a custom dimension.
A subset of the keyword data coming through looks like this:
09172018_rp_ws_1_og_
img s4_ac_p_act_
img s5_ws_5_m_
img s4_ws_5_m_
I am trying to use REGEXP_EXTRACT to create a new calculated field called "Image type" that is a dimension that groups all entries with starting with img, followed by a space, and then any alphanumeric afterwards ending with an underscore. So all entries with "img s4" would be grouped together, "img s5" would be grouped together. Anything in the keyword dimension without that pattern can be left out of the dataset entirely.
I am not able to get any results except "null" using REGEXP_EXTRACT.
Even just trying REGEXP_EXTRACT(Keyword, '.*img.*') yields null when entering in the formula for the new calculated field.
What is stumping me is I tried the following just to see if my syntax was off, and this formula does return results (just not what I want as the image types are not aggregated).
CASE
WHEN(REGEXP_MATCH(Keyword,'.*img.*')) THEN Keyword
ELSE "Not Set"
END
Any idea where I am going wrong? I can't get any output out of REGEXP_EXTRACT(Keyword, 'your reg expression here') no matter what I enter.
regex calculated-field google-data-studio
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
asked Nov 19 '18 at 20:43
SchipperSchipper
82
add a comment |
I am writing reports in Google's Data Studio. I have successfully created several custom dimensions in the past using REGEXP_MATCH on the 'Keyword' dimension combined with CASE statements to create the dimensions I need. This one has me stumped.
I have data coming in through the 'Keyword' dimension that contains a substring that I would like to extract and display as a custom dimension.
A subset of the keyword data coming through looks like this:
09172018_rp_ws_1_og_
img s4_ac_p_act_
img s5_ws_5_m_
img s4_ws_5_m_
I am trying to use REGEXP_EXTRACT to create a new calculated field called "Image type" that is a dimension that groups all entries with starting with img, followed by a space, and then any alphanumeric afterwards ending with an underscore. So all entries with "img s4" would be grouped together, "img s5" would be grouped together. Anything in the keyword dimension without that pattern can be left out of the dataset entirely.
I am not able to get any results except "null" using REGEXP_EXTRACT.
Even just trying REGEXP_EXTRACT(Keyword, '.*img.*') yields null when entering in the formula for the new calculated field.
What is stumping me is I tried the following just to see if my syntax was off, and this formula does return results (just not what I want as the image types are not aggregated).
CASE
WHEN(REGEXP_MATCH(Keyword,'.*img.*')) THEN Keyword
ELSE "Not Set"
END
Any idea where I am going wrong? I can't get any output out of REGEXP_EXTRACT(Keyword, 'your reg expression here') no matter what I enter.
regex calculated-field google-data-studio
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
asked Nov 19 '18 at 20:43
SchipperSchipper
82
TryREGEXP_EXTRACT(Keyword, 'img ([a-zA-Z0-9_]+)'). Also, try'img\s+(\w+)'
– Wiktor Stribiżew
Nov 19 '18 at 20:47
Both of those were close - thank you! They didn't strip out the rest of the data after encountering the first "", but they did return data. I tried: REGEXP_EXTRACT(Keyword, '^(img .+?)') and that worked - something about that first ^. I think it's time to study up on my RE2 regex expressions.
– Schipper
Nov 19 '18 at 20:56
So, what is the rule then?imgat the beginning, then 1+ whitespace and then all up to the first .... What char? Or up to the end of the line? LikeREGEXP_EXTRACT(Keyword, 'img\s+(.+)')? See regex101.com/r/5y893C/2
– Wiktor Stribiżew
Nov 19 '18 at 20:58
img is at the beginning in all of the cases I have seen, but in the future I believe it might be found in the middle of the keyword string, so I was hoping to find this string "img<space><anynumberof alphanumeric characters here><underscore>" and extract that from anywhere in the original string.
– Schipper
Nov 19 '18 at 21:29
add a comment |
I am writing reports in Google's Data Studio. I have successfully created several custom dimensions in the past using REGEXP_MATCH on the 'Keyword' dimension combined with CASE statements to create the dimensions I need. This one has me stumped.
I have data coming in through the 'Keyword' dimension that contains a substring that I would like to extract and display as a custom dimension.
A subset of the keyword data coming through looks like this:
09172018_rp_ws_1_og_
img s4_ac_p_act_
img s5_ws_5_m_
img s4_ws_5_m_
I am trying to use REGEXP_EXTRACT to create a new calculated field called "Image type" that is a dimension that groups all entries with starting with img, followed by a space, and then any alphanumeric afterwards ending with an underscore. So all entries with "img s4" would be grouped together, "img s5" would be grouped together. Anything in the keyword dimension without that pattern can be left out of the dataset entirely.
I am not able to get any results except "null" using REGEXP_EXTRACT.
Even just trying REGEXP_EXTRACT(Keyword, '.*img.*') yields null when entering in the formula for the new calculated field.
What is stumping me is I tried the following just to see if my syntax was off, and this formula does return results (just not what I want as the image types are not aggregated).
CASE
WHEN(REGEXP_MATCH(Keyword,'.*img.*')) THEN Keyword
ELSE "Not Set"
END
Any idea where I am going wrong? I can't get any output out of REGEXP_EXTRACT(Keyword, 'your reg expression here') no matter what I enter.
regex calculated-field google-data-studio
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
asked Nov 19 '18 at 20:43
SchipperSchipper
82
I am writing reports in Google's Data Studio. I have successfully created several custom dimensions in the past using REGEXP_MATCH on the 'Keyword' dimension combined with CASE statements to create the dimensions I need. This one has me stumped.
I have data coming in through the 'Keyword' dimension that contains a substring that I would like to extract and display as a custom dimension.
A subset of the keyword data coming through looks like this:
09172018_rp_ws_1_og_
img s4_ac_p_act_
img s5_ws_5_m_
img s4_ws_5_m_
I am trying to use REGEXP_EXTRACT to create a new calculated field called "Image type" that is a dimension that groups all entries with starting with img, followed by a space, and then any alphanumeric afterwards ending with an underscore. So all entries with "img s4" would be grouped together, "img s5" would be grouped together. Anything in the keyword dimension without that pattern can be left out of the dataset entirely.
I am not able to get any results except "null" using REGEXP_EXTRACT.
Even just trying REGEXP_EXTRACT(Keyword, '.*img.*') yields null when entering in the formula for the new calculated field.
What is stumping me is I tried the following just to see if my syntax was off, and this formula does return results (just not what I want as the image types are not aggregated).
CASE
WHEN(REGEXP_MATCH(Keyword,'.*img.*')) THEN Keyword
ELSE "Not Set"
END
Any idea where I am going wrong? I can't get any output out of REGEXP_EXTRACT(Keyword, 'your reg expression here') no matter what I enter.
regex calculated-field google-data-studio
regex calculated-field google-data-studio
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
asked Nov 19 '18 at 20:43
SchipperSchipper
82
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
asked Nov 19 '18 at 20:43
SchipperSchipper
82
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
edited Nov 19 '18 at 20:46
Wiktor Stribiżew
313k16133210
313k16133210
asked Nov 19 '18 at 20:43
SchipperSchipper
82
asked Nov 19 '18 at 20:43
SchipperSchipper
82
asked Nov 19 '18 at 20:43
SchipperSchipper
82
82
TryREGEXP_EXTRACT(Keyword, 'img ([a-zA-Z0-9_]+)'). Also, try'img\s+(\w+)'
– Wiktor Stribiżew
Nov 19 '18 at 20:47
Both of those were close - thank you! They didn't strip out the rest of the data after encountering the first "", but they did return data. I tried: REGEXP_EXTRACT(Keyword, '^(img .+?)') and that worked - something about that first ^. I think it's time to study up on my RE2 regex expressions.
– Schipper
Nov 19 '18 at 20:56
So, what is the rule then?imgat the beginning, then 1+ whitespace and then all up to the first .... What char? Or up to the end of the line? LikeREGEXP_EXTRACT(Keyword, 'img\s+(.+)')? See regex101.com/r/5y893C/2
– Wiktor Stribiżew
Nov 19 '18 at 20:58
img is at the beginning in all of the cases I have seen, but in the future I believe it might be found in the middle of the keyword string, so I was hoping to find this string "img<space><anynumberof alphanumeric characters here><underscore>" and extract that from anywhere in the original string.
– Schipper
Nov 19 '18 at 21:29
add a comment |
TryREGEXP_EXTRACT(Keyword, 'img ([a-zA-Z0-9_]+)'). Also, try'img\s+(\w+)'
– Wiktor Stribiżew
Nov 19 '18 at 20:47
Both of those were close - thank you! They didn't strip out the rest of the data after encountering the first "", but they did return data. I tried: REGEXP_EXTRACT(Keyword, '^(img .+?)') and that worked - something about that first ^. I think it's time to study up on my RE2 regex expressions.
– Schipper
Nov 19 '18 at 20:56
So, what is the rule then?imgat the beginning, then 1+ whitespace and then all up to the first .... What char? Or up to the end of the line? LikeREGEXP_EXTRACT(Keyword, 'img\s+(.+)')? See regex101.com/r/5y893C/2
– Wiktor Stribiżew
Nov 19 '18 at 20:58
img is at the beginning in all of the cases I have seen, but in the future I believe it might be found in the middle of the keyword string, so I was hoping to find this string "img<space><anynumberof alphanumeric characters here><underscore>" and extract that from anywhere in the original string.
– Schipper
Nov 19 '18 at 21:29
Try
REGEXP_EXTRACT(Keyword, 'img ([a-zA-Z0-9_]+)'). Also, try 'img\s+(\w+)'– Wiktor Stribiżew
Nov 19 '18 at 20:47
Try
REGEXP_EXTRACT(Keyword, 'img ([a-zA-Z0-9_]+)'). Also, try 'img\s+(\w+)'– Wiktor Stribiżew
Nov 19 '18 at 20:47
Both of those were close - thank you! They didn't strip out the rest of the data after encountering the first "", but they did return data. I tried: REGEXP_EXTRACT(Keyword, '^(img .+?)') and that worked - something about that first ^. I think it's time to study up on my RE2 regex expressions.
– Schipper
Nov 19 '18 at 20:56
Both of those were close - thank you! They didn't strip out the rest of the data after encountering the first "", but they did return data. I tried: REGEXP_EXTRACT(Keyword, '^(img .+?)') and that worked - something about that first ^. I think it's time to study up on my RE2 regex expressions.
– Schipper
Nov 19 '18 at 20:56
So, what is the rule then?
img at the beginning, then 1+ whitespace and then all up to the first .... What char? Or up to the end of the line? Like REGEXP_EXTRACT(Keyword, 'img\s+(.+)')? See regex101.com/r/5y893C/2– Wiktor Stribiżew
Nov 19 '18 at 20:58
So, what is the rule then?
img at the beginning, then 1+ whitespace and then all up to the first .... What char? Or up to the end of the line? Like REGEXP_EXTRACT(Keyword, 'img\s+(.+)')? See regex101.com/r/5y893C/2– Wiktor Stribiżew
Nov 19 '18 at 20:58
img is at the beginning in all of the cases I have seen, but in the future I believe it might be found in the middle of the keyword string, so I was hoping to find this string "img<space><anynumberof alphanumeric characters here><underscore>" and extract that from anywhere in the original string.
– Schipper
Nov 19 '18 at 21:29
img is at the beginning in all of the cases I have seen, but in the future I believe it might be found in the middle of the keyword string, so I was hoping to find this string "img<space><anynumberof alphanumeric characters here><underscore>" and extract that from anywhere in the original string.
– Schipper
Nov 19 '18 at 21:29
add a comment |
1 Answer
1
active
oldest
votes
Mind that in order to extract any text from REGEXP_EXTRACT, you should define a capturing group inside the regex pattern. In short, enclose the part you need to extract with a pair of unescaped parentheses.
Now, to match img at the start of the string you need to use ^ anchor, it matches the start of a string position.
To match 1 or more chars, use +.
So, you may use any of the following depending on your actual rules:
REGEXP_EXTRACT(Keyword, '^img ([a-zA-Z0-9_]+)')
REGEXP_EXTRACT(Keyword, '^img\s+(\w+)')
REGEXP_EXTRACT(Keyword, '^img\s+(.+)')
Details
^- start of string
img- a literal substring
([a-zA-Z0-9_]+)- Capturing group 1: one or more letters, digits or_
s+- 1 or more whitespaces
w+- 1 or more word chars: letters, digits or_
.+- 1 or more chars other than line break chars.
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
1
Fantastic. I didn't escape out the s in one of my test runs, so that is very helpful.
– Schipper
Nov 19 '18 at 21:31
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382364%2fcorrect-usage-for-regexp-extract-in-google-data-studio-to-create-a-calculated-fi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Mind that in order to extract any text from REGEXP_EXTRACT, you should define a capturing group inside the regex pattern. In short, enclose the part you need to extract with a pair of unescaped parentheses.
Now, to match img at the start of the string you need to use ^ anchor, it matches the start of a string position.
To match 1 or more chars, use +.
So, you may use any of the following depending on your actual rules:
REGEXP_EXTRACT(Keyword, '^img ([a-zA-Z0-9_]+)')
REGEXP_EXTRACT(Keyword, '^img\s+(\w+)')
REGEXP_EXTRACT(Keyword, '^img\s+(.+)')
Details
^- start of string
img- a literal substring
([a-zA-Z0-9_]+)- Capturing group 1: one or more letters, digits or_
s+- 1 or more whitespaces
w+- 1 or more word chars: letters, digits or_
.+- 1 or more chars other than line break chars.
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
1
Fantastic. I didn't escape out the s in one of my test runs, so that is very helpful.
– Schipper
Nov 19 '18 at 21:31
add a comment |
Mind that in order to extract any text from REGEXP_EXTRACT, you should define a capturing group inside the regex pattern. In short, enclose the part you need to extract with a pair of unescaped parentheses.
Now, to match img at the start of the string you need to use ^ anchor, it matches the start of a string position.
To match 1 or more chars, use +.
So, you may use any of the following depending on your actual rules:
REGEXP_EXTRACT(Keyword, '^img ([a-zA-Z0-9_]+)')
REGEXP_EXTRACT(Keyword, '^img\s+(\w+)')
REGEXP_EXTRACT(Keyword, '^img\s+(.+)')
Details
^- start of string
img- a literal substring
([a-zA-Z0-9_]+)- Capturing group 1: one or more letters, digits or_
s+- 1 or more whitespaces
w+- 1 or more word chars: letters, digits or_
.+- 1 or more chars other than line break chars.
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
1
Fantastic. I didn't escape out the s in one of my test runs, so that is very helpful.
– Schipper
Nov 19 '18 at 21:31
add a comment |
Mind that in order to extract any text from REGEXP_EXTRACT, you should define a capturing group inside the regex pattern. In short, enclose the part you need to extract with a pair of unescaped parentheses.
Now, to match img at the start of the string you need to use ^ anchor, it matches the start of a string position.
To match 1 or more chars, use +.
So, you may use any of the following depending on your actual rules:
REGEXP_EXTRACT(Keyword, '^img ([a-zA-Z0-9_]+)')
REGEXP_EXTRACT(Keyword, '^img\s+(\w+)')
REGEXP_EXTRACT(Keyword, '^img\s+(.+)')
Details
^- start of string
img- a literal substring
([a-zA-Z0-9_]+)- Capturing group 1: one or more letters, digits or_
s+- 1 or more whitespaces
w+- 1 or more word chars: letters, digits or_
.+- 1 or more chars other than line break chars.
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
Mind that in order to extract any text from REGEXP_EXTRACT, you should define a capturing group inside the regex pattern. In short, enclose the part you need to extract with a pair of unescaped parentheses.
Now, to match img at the start of the string you need to use ^ anchor, it matches the start of a string position.
To match 1 or more chars, use +.
So, you may use any of the following depending on your actual rules:
REGEXP_EXTRACT(Keyword, '^img ([a-zA-Z0-9_]+)')
REGEXP_EXTRACT(Keyword, '^img\s+(\w+)')
REGEXP_EXTRACT(Keyword, '^img\s+(.+)')
Details
^- start of string
img- a literal substring
([a-zA-Z0-9_]+)- Capturing group 1: one or more letters, digits or_
s+- 1 or more whitespaces
w+- 1 or more word chars: letters, digits or_
.+- 1 or more chars other than line break chars.
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
answered Nov 19 '18 at 21:19
Wiktor StribiżewWiktor Stribiżew
313k16133210
313k16133210
1
Fantastic. I didn't escape out the s in one of my test runs, so that is very helpful.
– Schipper
Nov 19 '18 at 21:31
add a comment |
1
Fantastic. I didn't escape out the s in one of my test runs, so that is very helpful.
– Schipper
Nov 19 '18 at 21:31
1
1
Fantastic. I didn't escape out the s in one of my test runs, so that is very helpful.
– Schipper
Nov 19 '18 at 21:31
Fantastic. I didn't escape out the s in one of my test runs, so that is very helpful.
– Schipper
Nov 19 '18 at 21:31
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382364%2fcorrect-usage-for-regexp-extract-in-google-data-studio-to-create-a-calculated-fi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try
REGEXP_EXTRACT(Keyword, 'img ([a-zA-Z0-9_]+)'). Also, try'img\s+(\w+)'– Wiktor Stribiżew
Nov 19 '18 at 20:47
Both of those were close - thank you! They didn't strip out the rest of the data after encountering the first "", but they did return data. I tried: REGEXP_EXTRACT(Keyword, '^(img .+?)') and that worked - something about that first ^. I think it's time to study up on my RE2 regex expressions.
– Schipper
Nov 19 '18 at 20:56
So, what is the rule then?
imgat the beginning, then 1+ whitespace and then all up to the first .... What char? Or up to the end of the line? LikeREGEXP_EXTRACT(Keyword, 'img\s+(.+)')? See regex101.com/r/5y893C/2– Wiktor Stribiżew
Nov 19 '18 at 20:58
img is at the beginning in all of the cases I have seen, but in the future I believe it might be found in the middle of the keyword string, so I was hoping to find this string "img<space><anynumberof alphanumeric characters here><underscore>" and extract that from anywhere in the original string.
– Schipper
Nov 19 '18 at 21:29