Magic ETL

Magic ETL

Extracting the Date Based on a pattern

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:

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • Coach
    Answer ✓

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

    1. CASE WHEN LEFT(`ORDERSTATUS`,1) ='*' OR `ORDERSTATUS` like '%BAM%'
    2.  
    3.  THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')
    4.  
    5.       ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    6.  
    7.  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

  • 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

    1. -- Name your beastmode Soldorstockdate
    2. CASE
    3.     WHEN INSTR(`orderstatus`, '*') >= 1
    4.     THEN DATE_FORMAT(RIGHT(TRIM(`orderstatus`),10),'%m/%d/%Y')
    5.     ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    6.     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

    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

    1. -- name your beastmode Soldorstockdate
    2. CASE
    3.     WHEN INSTR(`orderstatus`, '*') >= 1
    4.     THEN DATE_FORMAT(RIGHT(TRIM(`orderstatus`),10),'%m/%d/%Y')
    5.     ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    6.     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'
  • 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

  • Coach
    Answer ✓

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

    1. CASE WHEN LEFT(`ORDERSTATUS`,1) ='*' OR `ORDERSTATUS` like '%BAM%'
    2.  
    3.  THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')
    4.  
    5.       ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    6.  
    7.  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'

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In