I want to understand RegEx! (Part2)

It is already two weeks ago since my last blog post. This two weeks I spent in Scotland. It is a very beautiful country with unforgettable landscaps. However, it is not the topic I want to talk about. In this blog I will continue the topic about the RegEx and its using in Tableau.

Tableau offers you four different uses cases of RegEx:
REGEXP_EXTRACT
If you have data like this: aspirin_20_mg_oral_tablet and you would like to extract onliy 20_mg.
What I do is I check my RegEx on this web site: http://regexr.com/

If it is ok, put this RegEx in Tableau function: REGEXP_EXTRACT([0-9][0-9]+[_]+[a-z]+) and extract all "20_mg"

REGEXP_EXTRACT_NTH

The same like the function before but here you can limit your extract to Nth index. Image you have data like this: aspirin_20565435 and you want to extract only "aspirin_20". So your formula should be: REGEXP_EXTRACT_NTH([a-z]+[0-9], 2)

REGEXP_MATCH
It is very easy one: Using REGEXP_MATCH we can find if the string the matches with entered pattern or not. If the string matches with the pattern it return true else false.
REGEXP_REPLACE
If you would like to replace somethin into something, then use this function.

Example: I want to replace all "aspirin_20_mg_oral_tablet" by space " ". My RegEx is:

REGEXP_REPLACE ([a-z0-9], ' ')

Here you can find more to RegEx:

Regular Expressions In Tableau

Additional Functions in Tableau

Become regular with regular expressions

I want to understand RegEx (Part 1)

Best

Dilyana

Kommentare

Beliebte Posts aus diesem Blog

Tableau Table Calculation Function: WINDOW - Functions

The functions which begin with „WINDOW_...“ are also common used in Tableau. Remember! The “WINDOW_” function stays for the offset in data set, so-called WINDOW. It can look like this: 1) You can see the “WINDOW” clearly because of separation line between rows: 2) You limit the “Window” by giving the information about the first and the last row number. In this case, you give Tableau the information about the data offset. Let's have a look at the example with WINDOW_SUM I created a sample with data from Superstore. I would like to have a total sum of Sales in every row. In order to do this I created a calculation field: WINDOW_SUM(SUM([Sales]), FIRST(), LAST()) With this formula I said to Tableau: “Hey Tableau, calculate the total sum of sales from the first till the last row in the data set” And this is the result: Tableau wrote the result (total sum of Sales) in every row. As another option you can cumulate the result in each row and...

Tableau Number Function: ABS

I have been working with Tableau since 2014, but I have still a feeling that my knowledge about this software not good enough. When I rebuild dashboards from tableau.public I am fascinating how some people can create amazing formulas and thus it makes the calculation of data sets easier. Sometimes I got stuck by creating of graphs and I do some calculations with data in Excel before I visualize them. Otherwise, you can find the definition of every function by creating of calculation field. But, honestly, are this definitions always clearly enough? For me not and I am done by doing long calculations with Excel. 😠 I like my work and I want to have more fun and not do such long calculation with Excel! I took up a challenge and decided to go through every function in tableau in order to understand how they work. I would like to begin with ABS function. ABS function is very easy one, as it used to get an absolute value of a number from a negative one. As an examp...

Tableau Logical Functions: IF, IIF, IFNULL

Have you ever had the situations when you would like to add some conditions as a text or as a number in one separate column? And you got stuck on it. The solution is “IF”, “IIF”, and “IFNULL” functions. IF With the function IF you can add three or more conditions to your calculation: IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END EXAMPLE IF SUM([Profit])>5000 then "Profit" ELSEIF SUM([Profit])<4999 and SUM([Profit])>2000 then "Breakeven" ELSE "Loss" END Note! If you create this formula, use exact the aggregation in the text field. In the example above the aggregation is SUM([Profit]), thus I used this aggregation in the formula and NOT just [Profit]. IIF If you want to run a calculation with TWO conditions, e.g. : If I have “XY”, then give me “Z”, otherwise give me “AB” (two conditions!), then use the function IIF. EXAMPLE Assume, we have this data set: I would like to calculate the Conversi...

FlowerViz

Dieses Blog durchsuchen