RegEx - extracting text after a specific word

[rebeccarlisle]

Hi All

I am trying to extract text after the word "tasks" in the below table. Only where Field contains "tasks" do I want the value ".0." or ".1.". I have tried various different Regular Expressions using the RegEx tool but unable to output a value in a new field (it is coming out null or blank). Thanks in advance!

Field

hits.0._source.content.assignedTo

hits.0._source.content.deleted

hits.0._source.content.entities.596

hits.0._source.content.entities.598

hits.0._source.content.lastChange

hits.0._source.content.class

hits.0._source.content.tasks.0.deleted

hits.0._source.content.tasks.0.system

hits.0._source.content.tasks.0.teamToAction

hits.0._source.content.tasks.0.change

hits.0._source.content.tasks.0.id

hits.0._source.content.tasks.0.creationDate

hits.0._source.content.tasks.0.explanation

hits.0._source.content.tasks.0.closingDate

hits.0._source.content.tasks.0.entityIds.0

hits.0._source.content.tasks.0.entityIds.1

hits.0._source.content.tasks.0.status

hits.0._source.content.tasks.1.deleted

hits.0._source.content.tasks.1.system

hits.0._source.content.tasks.1.teamToAction

hits.0._source.content.tasks.1.change

hits.0._source.content.tasks.1.id

hits.0._source.content.tasks.1.creationDate

hits.0._source.content.tasks.1.explanation

hits.0._source.content.tasks.1.closingDate

hits.0._source.content.tasks.1.entityIds.0

hits.0._source.content.tasks.1.entityIds.1

hits.0._source.content.tasks.1.status

[LukeM · Accepted solution]

Hi @rebeccarlisle 

I always advise to use a website like https://regex101.com/ to write your RegEx before copy and pasting it into Alteryx. The guidance and explanation in there is much more comprehensive.

Using the Parse function in RegEx parse tool you use brackets () to signify which bit of text you want in a field.

I think the following code should work:

.+tasks\.([0-9]).+

From Regex101.com (the green is what we are parsing):

Let me know if you get this working or if you have any other questions.

Luke

[jaimonsk]

In order to extract data after "tasks" the following formula would help

If Contains([Field], "tasks") then Substring([Field],FindString([Field], "tasks")) else "" endif.

I did not understand the requirement after that.Could you give describe it gain with an example?

[LordNeilLord]

Hey @rebeccarlisle 

I know you have the solution for this but I think you might find an easier way to approach this JSON if you use text to columns first then you can crosstab your results easier.