Looking for Pattern in Data

Options
ozarkram
ozarkram Member
edited May 2022 in Magic ETL

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

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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)


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ozarkram
    ozarkram Member

    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
    MarkSnodgrass Coach

    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)


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories