我如何逃脱保留的单词用作列名称？ Google Data Studio

发布于 2025-01-23 04:20:19 字数 867 浏览 1 评论 0原文

我正在使用Google Data Studio生产一些数据仪表板，作为此部分的一部分，我正在数据集中创建一个计算出的字段，该字段从字母数字字符串中剥离了数值，即7A中的7分。

然后，我使用案例语句将另一个数字分配给条纹值，即公式返回7的案例语句将其分配给1000，8 AS 2000，9至3000，

我遇到的问题是在数据集中三个这些字段的标题为7、8和9。因此，在下面的公式中，前三行将条纹字段与标题为7、8或9的字段进行比较，而不是7、8或9的

数值能够逃脱公式中的7,8或9个字符，因此它们被视为数字值，而不是对数据集中的字段的参考。

case
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 7 then 1000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 8 then 2000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 9 then 3000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 10 then 4000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 11 then 5000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 12 then 6000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 13 then 7000
end

原文

I am using Google data studio to produce some data dashboards, as part of this I am creating a calculated field on the dataset that strips the numeric value from an alphanumeric string i.e. 7 out of 7A.

I'm then using a case statement to assign another number to the striped value i.e where the formula returns 7 the case statement assigns this to 1000, 8 as 2000, 9 to 3000 ect

The problem I am having is that in the dataset three of the fields are titled 7, 8 and 9. As such in the below formula the first three lines are comparing the striped field to the fields titled 7, 8 or 9 and not to the numeric value of 7, 8 or 9

I need to be able to escape the 7,8 or 9 characters in the formula so they are treated as a numeric value and not a referance to a field in the dataset.

case
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 7 then 1000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 8 then 2000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 9 then 3000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 10 then 4000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 11 then 5000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 12 then 6000
    when CAST(REGEXP_REPLACE(YearGroup, R"\D+", "") AS NUMBER)= 13 then 7000
end

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

荒岛晴空 2025-01-30 04:20:20

要剥离字符串的一部分，请使用REGEXP_EXTRACT。

首先检查一个数字，文本可能只包含数字。如果是一个数字，将文本字段转换为一个数字。

如果文本字段包含数字（\ d+），则是无数数字（\ d），则提取了数字。将其转换为一个数字并进行计算。

CASE
  WHEN REGEXP_MATCH(YearGroup, r"\d+") THEN CAST(YearGroup AS number )
  WHEN CAST(REGEXP_EXTRACT(YearGroup, r"(\d+)\D") AS number ) BETWEEN 7 AND 13 THEN 
    1000 * ( CAST(REGEXP_EXTRACT(YearGroup, r"(\d+)\w") AS number ) - 6 )
  ELSE NULL
END

To strip a part of a string, please use REGEXP_EXTRACT.

First check for a number, the text may only contain digits. If it is a number convert the text field to a number.

If the text field contains digits (\d+) followed by a none digit (\D), the extract the digits. Convert it to a number and do your calculation.

CASE
  WHEN REGEXP_MATCH(YearGroup, r"\d+") THEN CAST(YearGroup AS number )
  WHEN CAST(REGEXP_EXTRACT(YearGroup, r"(\d+)\D") AS number ) BETWEEN 7 AND 13 THEN 
    1000 * ( CAST(REGEXP_EXTRACT(YearGroup, r"(\d+)\w") AS number ) - 6 )
  ELSE NULL
END

回复收藏 0 原文

~没有更多了~