CODEWITHSUNDEEP
looker-studio
Mengezi Dhlomo
Mengezi Dhlomo

Reputation: 335

Concatenate Number and Text based on condition in Google Data Studio

I would like to concatenate a number and text based on a condition. For example:

CASE WHEN quantity = 1 THEN CONCAT(quantity, 'Item') ELSE CONCAT(quantity, 'Items') END

Result:

1 Item
2 Items
3 Items
...
20 Items

Presently, THEN/ELSE statements do not accept functions. Is there an alternative method to achieve the above result in data studio?

Upvotes: 1

Views: 1556

Answers (1)

Nimantha
Nimantha

Reputation: 6471

One way it can be achieved is by creating the below Calculated Field which uses the CONCAT function to join the Quantity field with the text Items (note that the single space before Items is intentional) and then the REGEXP_REPLACE function to ensure that 1 Items is replaced by 1 Item:

REGEXP_REPLACE(CONCAT(quantity, " Items"), "^(1 Items)$", "1 Item")

Google Data Studio Report and a GIF to elaborate:

Upvotes: 1

Related Questions