Locate and return a substring within a column

Question & Answer
, ,
1

I am trying to parse out part of a column that is a long string. I need the 2024-07-29 out of the below string. The column looks like this:

\n Purpose: Improvement of line handling, easier adjustments and solving of known problems, This update will i\n \n Description:\n for details see attached file\n Description to long for this field.\n \n Proposed Start Date: 2024-07-29T00:00:00.000Z\n \n Impact/Risk\n \n Impacted machines

I have tried multiple substring and locate variations like this: substring({description}, locate(‘Proposed start date:’, {description})+18, 10), but this formula gives me a return of “attached f”.

and tried a solution from here: Conditional extraction from a string between two characters that seems to have an issue. I also tried using parsejson but it says it cannot be used with a Spice dataset.

Any help would be greatly appreciated.

2

Hi @Owen ,

Welcome back to the community!!

I believe locate is case sensitive and thus is unable to locate the text. Can you try this formula instead and share what you get?

substring({description},locate(toLower({description}),‘proposed start date:’)+21,10)

Thanks,
Prantika

1 Like
3

Hi @prantika_sinha thank you so much for taking a look at the issue. Unfortunately I could not get it to work. It was throwing up a syntax error. I tried to do multiple variations but the calculated fields would not accept the formula.

4

Will you be able to share a sample dashboard in the Arena with the calculated field you are trying?

You may need to condition the formula as if locate returns 0, then it should return blank.

5

I will work on putting it into arena. Some of the information is from vendors so I want to make something that does not have sensitive information. Should have it ready tomorrow. Thank you again for your help!

6

Hi @Owen

I think I know why you got syntax errors while using the solution given by @prantika_sinha.

When you copy the formula from her reply and paste it into the calculated field the single quotes take a slightly different form.
image

If you remove those and re-type the single quotes you should be OK with the formula she has given. I tried it with a few variations of text and found one issue that if the locate does not find anything, but the text is longer than 21 characters it will still get some text. So included a contains clause to check that your key starting point (Proposed Start Date) exists in the text and then only proceed with the substring

image
image731×66 3.63 KB

image
image1524×378 15.5 KB

1 Like
7

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!