Looking for Pattern in Data

ozarkram · 2022-05-26T19:37:09+00:00

There was an error rendering this rich post.

Hi All,

One of the fields (orderstatus) in my report has values like 37141 SLD BAM , SOLD BRANDON BOX 36790...I need to look for values SLD and SOLD ..If these are present then I need to flag that row as Sold otherwise it is stock...Can you please let me know how to achieve this using Beast mode

Find more posts tagged with

Beast Mode

Accepted answers

All comments

MarkSnodgrass

You can use a case statement to evaluate this and look for SLD and SOLD. It would look like this:

(CASE when `orderstatus` like '%SLD%' THEN 'Sold'
when `orderstatus` like '%SOLD%' THEN 'Sold'
ELSE 'Stock'
END)

ozarkram

Hi @MarkSnodgrass , Thank you so much for your response!..I also want to check if the first character is a * then I want to flag it 1.. For ex: *SLD 05/11/2022 - I need to flag this 1 ..*SOLD 05/15/2022 -I need to flag this 1 ,1277489 STK#39298.32831 * -I need to flag this 0..can you please let me know how to do this..

MarkSnodgrass

If the * is always right next to SLD or SOLD then you could just tweak the beast mode to be like this:

(CASE when `orderstatus` like '%*SLD%' THEN 'Sold'
when `orderstatus` like '%*SOLD%' THEN 'Sold'
ELSE 'Stock'
END)

If it can be anywhere in the string but also needs to contain SLD or SOLD then you could do this:

(CASE when `orderstatus` like '%SLD%' AND `orderstatus` like '%*%' THEN 'Sold'
when `orderstatus` like '%SOLD%' AND `orderstatus` like '%*%' THEN 'Sold'
ELSE 'Stock'
END)