Regular Expression in Calculated Fields?

[praveen.gp]

I have a use case to use the regular expressions in the calculated field. But not finding the regex function in QuickSight.

I see the below posts regarding that. any updates please. It would be great if we have the regex function available soon.

Regular expression in a calculated field

Hi I would like to search a field based on a regular expression for example, if I have a series of urls I would like to search for all the urls that have the word “cio” inside. but I don’t want to consider other words like “calcio” or “cacio”. basically I would like to search for a word in a string based on a regular expression I was expecting something like this but it doesn’t work sumIf({page views}, {url}=“^(cio)?” ) the calculated field locate or contains fields I do not consider them…

Replace string / Regex

I’m trying to clean up an identifier column with a prefix at the start of the string. For example, Input a) AB1001 b) BAC1002 c) AA102E Output a) 1001 b) 1002 c)102E I couldn’t find a regex function in Quick Sight to do this (i thought this is a very basic function). The alternative is to use the ‘replace’ function, but I have over 20 prefix combinations so it is not efficient. I also don’t have access to the SQL to use regexp_replace. Appreciate it if anyone has a solution to this? T…

[Brett]

Hi @praveen.gp,
Unfortunately, the ‘regex’ function, or something similar to, is still a feature limitation in Quick Sight. I had suggested a few work arounds in regards to regex functions at the post below:

Tableau To Quick Sight Migration Function Support - Space, char etc

Hi @anshul1243, As you’ve noticed, Quick Sight and Tableau have different function lists so there may not be a 1:1 replacement in Quick Sight to match your Tableau function. As I do not work with Tableau functions very often, could you please share some explanation of what you’re trying to complete with each function if the suggestions do not seem plausible? Space: You can add spaces to a field by utilizing the concat function Regex_Extract: You could try and explore using a combination of sp…

If you could explain your scenario that requires this function a bit more in depth, what you’re trying to complete and what an ideal outcome is, I can try and suggest additional work arounds that could be used.

Additionally, I’ll keep this tagged as a feature request to promote visibility to the support team.

[praveen.gp]

I want to capture a value for my field which is only in this format ‘12-AB-1234’ all the other values should be as ‘- -’.
I have the other values like ‘1234’, ‘New’, Nulls.,
Right now I use contains to capture those required format. But If I have Regex available. The calculated field is straight.

The calculated field Which i am using for my testing. Please suggest if there is any better way to use it.

ifelse(
isNull({Value})
OR toString({Value}) = ‘0’
OR isNull({Type_CD})
OR {Type_CD} = ‘0’
OR {Record_Type} = ‘–’,
‘–’,
ifelse(
{Type_CD} = ‘F’,
ifelse(
contains(toString({Value}), ‘–’)
OR contains(toString({Value}),‘A’) OR contains(toString({Value}),‘a’)
OR contains(toString({Value}),‘B’) OR contains(toString({Value}),‘b’)
OR contains(toString({Value}),‘C’) OR contains(toString({Value}),‘c’)
OR contains(toString({Value}),‘D’) OR contains(toString({Value}),‘d’)
OR contains(toString({Value}),‘E’) OR contains(toString({Value}),‘e’)
OR contains(toString({Value}),‘F’) OR contains(toString({Value}),‘f’)
OR contains(toString({Value}),‘G’) OR contains(toString({Value}),‘g’)
OR contains(toString({Value}),‘H’) OR contains(toString({Value}),‘h’)
OR contains(toString({Value}),‘I’) OR contains(toString({Value}),‘i’)
OR contains(toString({Value}),‘J’) OR contains(toString({Value}),‘j’)
OR contains(toString({Value}),‘K’) OR contains(toString({Value}),‘k’)
OR contains(toString({Value}),‘L’) OR contains(toString({Value}),‘l’)
OR contains(toString({Value}),‘M’) OR contains(toString({Value}),‘m’)
OR contains(toString({Value}),‘N’) OR contains(toString({Value}),‘n’)
OR contains(toString({Value}),‘O’) OR contains(toString({Value}),‘o’)
OR contains(toString({Value}),‘P’) OR contains(toString({Value}),‘p’)
OR contains(toString({Value}),‘Q’) OR contains(toString({Value}),‘q’)
OR contains(toString({Value}),‘R’) OR contains(toString({Value}),‘r’)
OR contains(toString({Value}),‘S’) OR contains(toString({Value}),‘s’)
OR contains(toString({Value}),‘T’) OR contains(toString({Value}),‘t’)
OR contains(toString({Value}),‘U’) OR contains(toString({Value}),‘u’)
OR contains(toString({Value}),‘V’) OR contains(toString({Value}),‘v’)
OR contains(toString({Value}),‘W’) OR contains(toString({Value}),‘w’)
OR contains(toString({Value}),‘X’) OR contains(toString({Value}),‘x’)
OR contains(toString({Value}),‘Y’) OR contains(toString({Value}),‘y’)
OR contains(toString({Value}),‘Z’) OR contains(toString({Value}),‘z’),
toString({Value}),
‘–’
),
toString({Value})
)
)

[Brett]

Hi @praveen.gp,
Apologies for missing your last response on this!
While a bit tedious and long, this logic seems accurate to me, did it work out in your scenario?

[praveen.gp]

Yeah it worked for my scenario.