Extracting the Date Based on a pattern

ozarkram
ozarkram Member
in Magic ETL

Hi All,

I am trying to extract the date from one column based on a pattern..For example in the table below

image.png

if the column orderstatus has a * in the beginning and then there is a date of the format mm/dd/yyyy then I need to extract that date as the soldorstockdate otherwise I need to extract the date from orddate as the soldorstockdate...Can you please let me know how to do this...


Thanks,

Arun

Tagged:

Best Answer

  • Godiepi
    Godiepi Coach
    Answer ✓

    Yes no problem , no need to say WHEN again ... This way should clear out the error

    CASE WHEN LEFT(`ORDERSTATUS`,1) ='*' OR `ORDERSTATUS` like '%BAM%'

 THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')

      ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')

 end
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Answers

  • Godiepi
    Godiepi Coach

    Hi @ozarkram

    if always the date you need is present at the end of the string and the identifier is * then first check for that asterisk character present, then get the last 10 of the string converting it to date format mm/dd/yyyy else use the column orddate

    this beastmode should do the job

     -- Name your beastmode Soldorstockdate
CASE
    WHEN INSTR(`orderstatus`, '*') >= 1
    THEN DATE_FORMAT(RIGHT(TRIM(`orderstatus`),10),'%m/%d/%Y')
    ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    END
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • Godiepi
    Godiepi Coach

    @ozarkram

    1. check for the * character in the string
    2. if present then get the last 10 characters from the strings (if always is the date at the end)
    3. convert it to a date format month , day and year
    4. if the character * is not present then use the other column

    This beastmode should do the job 

    -- name your beastmode Soldorstockdate
CASE
    WHEN INSTR(`orderstatus`, '*') >= 1
       THEN DATE_FORMAT(RIGHT(TRIM(`orderstatus`),10),'%m/%d/%Y')
       ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    END
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • ozarkram
    ozarkram Member
    edited May 2022

    Hi @Godiepi ...Really appreciate you getting back!..Can you please let me know what is the error with below code:


    CASE WHEN LEFT(`ORDERSTATUS`,1)='*' OR

     WHEN `ORDERSTATUS` like '%BAM%'

     THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')

          ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')

     end

  • Godiepi
    Godiepi Coach
    Answer ✓

    Yes no problem , no need to say WHEN again ... This way should clear out the error

    CASE WHEN LEFT(`ORDERSTATUS`,1) ='*' OR `ORDERSTATUS` like '%BAM%'

 THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')

      ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')

 end
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Categories