I have a table in Big Query and for simplicity lets say it has 2 columns
id , result
The value in result could be success, fail , cancel.
Now I want to have a table chart in Google Data Studio like following
Only issue is how to add Attempt to the table.
Basically what I am looking for is a condition something like
If result =success | fail | cancel then "Attempt"
If result = success then "Success"
If result = fail then "Fail"
If result = cancel then "Cancel"
Pls. let me know if it is possible to make such Dimension.
I think you cantry to create a new dimension based on a CASE WHEN + REGEXP formula to achieve your goal.
To do that, you should edit your data source and click on the "+" icon "create new field". You will call it "Category" and then the formula could look like this (Sorry I'm not 100% sure of the Regex writing).
CASE WHEN REGEXP_MATCH(result, "(success | fail | cancel)") THEN "attempt" WHEN REGEXP_MATCH(Result, "(sucess*)" ) THEN "Success" WHEN REGEXP_MATCH(result, "(Fail*)" ) THEN "Fail" WHEN REGEXP_MATCH(result, "(Cancel*)" ) THEN "Cancel" ELSE "other" END
Hope it will help!
Have a nice day,
Thanks Anthony for the reply.
I tried the formula below
WHEN REGEXP_MATCH(result, "(success|failed|canceled)") THEN "Attempt"
WHEN REGEXP_MATCH(result, "(success*)" ) THEN "Success"
WHEN REGEXP_MATCH(result, "(failed*)" ) THEN "Failed"
WHEN REGEXP_MATCH(result, "(canceled*)" ) THEN "Canceled"
With this formula for all records the first When always matches the criteria and only Attempt is shown.
Success, Failed and Canceled are never shown.
Did I miss something?
Sorry for my lack of response yesterday.
I'm not 100% sure of the reason why the formula fails (probably related to the fact that the 4 conditions produce overlapping results).
I don't know if it will meet your needs, but as a workaround, you could try to build the 4 metrics individually with a unique CASE condition for each. And then, add the 4 metrics to your table.
Have a great day!