Beast mode to extract the date based on a pattern

ozarkram
ozarkram Member
in Magic ETL

Hi All,

I am trying to extract the date from a field based on a pattern - If the first four characters of the field description are *SLD then I need to extract the date from the field description as output date else I need to take the date from the field orddate as the the output date..please find the table below:

image.png


can you please provide some insights here..

Sample sheet.xlsx


Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Try this to deal with the additional scenarios

    DATE(
 CASE WHEN LEFT(`description`,5) = '*SLD ' THEN
	IFNULL(TRY_CAST(SPLIT_PART(`description`,' ',2) AS DATE),`orddate`)
 WHEN LEFT(`description`,4) = '*SLD' THEN
 	IFNULL(TRY_CAST(SPLIT_PART(REPLACE(`description`,'*SLD',''),' ',1) AS DATE),`orddate`)
 ELSE 
 	`orddate`
 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.
  • GrantSmith
    GrantSmith Coach
    edited June 2022 Answer ✓

    @ozarkram

    Is your date always in the format of MM/DD/YY? The following regular expression will handle MM/DD/YY and MM/DD/YYYY formats.

    I'd recommend doing this in a Magic ETL as it'll reduce the processing needed when displaying your data and not have to calculate it every time you load your cards. You can use a formula tile and a regular expression to extract the date out of your string. You can then compare the extracted version to the original version - if they're the same the regex failed and then you can use the orddate:

    extracted_date

    REGEXP_REPLACE(`description`, '^.*(\d{1,2}\/\d{1,2}\/(\d{2})|(\d{4})).*$', '$1')

    date

    IF `extracted_date` = `description` THEN `orddate` ELSE `extracted_date` END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    Based on your sample data, this logic should work to extract the date from the description field when it starts with *SLD

    DATE(CASE WHEN LEFT(`description`,4) = '*SLD' THEN
	SPLIT_PART(`description`,' ',2)
 ELSE 
 	`orddate`
 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 have also cases when data is like

    *SLD5/17/22 jilo ( here there is no space between SLD and the date )

    Also does this formula append 2022....

    In some cases the field has data like.. *SLD owell23451 In this case I should consider only the orddate...Is this possible..

  • MarkSnodgrass
    MarkSnodgrass Coach

    @ozarkram if your data has no consistency or pattern as to where the date is going to be, then it is going to be extremely difficult to construct the correct statement to extract it. If you can identify all the possibilities, then you might have a chance.

    **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

    The other two possibilities are only *SLD5/17/22 jilo (no space after SLD ) and then no date *SLD owell23451 ( here I should consider the ord date)

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Try this to deal with the additional scenarios

    DATE(
 CASE WHEN LEFT(`description`,5) = '*SLD ' THEN
	IFNULL(TRY_CAST(SPLIT_PART(`description`,' ',2) AS DATE),`orddate`)
 WHEN LEFT(`description`,4) = '*SLD' THEN
 	IFNULL(TRY_CAST(SPLIT_PART(REPLACE(`description`,'*SLD',''),' ',1) AS DATE),`orddate`)
 ELSE 
 	`orddate`
 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.
  • GrantSmith
    GrantSmith Coach
    edited June 2022 Answer ✓

    @ozarkram

    Is your date always in the format of MM/DD/YY? The following regular expression will handle MM/DD/YY and MM/DD/YYYY formats.

    I'd recommend doing this in a Magic ETL as it'll reduce the processing needed when displaying your data and not have to calculate it every time you load your cards. You can use a formula tile and a regular expression to extract the date out of your string. You can then compare the extracted version to the original version - if they're the same the regex failed and then you can use the orddate:

    extracted_date

    REGEXP_REPLACE(`description`, '^.*(\d{1,2}\/\d{1,2}\/(\d{2})|(\d{4})).*$', '$1')

    date

    IF `extracted_date` = `description` THEN `orddate` ELSE `extracted_date` END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ozarkram
    ozarkram Member

    Thank you so much @MarkSnodgrass and @GrantSmith ...Really appreciate your help!

  • ozarkram
    ozarkram Member

    @GrantSmith : the below function that you shared is not looking for *SLD at all.. Can you please let me know


    REGEXP_REPLACE(`description`, '^.*(\d{1,2}\/\d{1,2}\/(\d{2})|(\d{4})).*$', '$1')

  • ozarkram
    ozarkram Member
    edited June 2022

    @MarkSnodgrass : I am getting the following error when I try to do the code below:

    Error : For 'isnull', types date and varchar are inconsistent

    Do you have any suggestions

    1. DATE(
    2.  CASE WHEN LEFT(`description`,5) = '*SLD ' THEN
    3. IFNULL(TRY_CAST(SPLIT_PART(`description`,' ',2) AS DATE),`orddate`)
    4.  WHEN LEFT(`description`,4) = '*SLD' THEN
    5.   IFNULL(TRY_CAST(SPLIT_PART(REPLACE(`description`,'*SLD',''),' ',1) AS DATE),`orddate`)
    6.  ELSE 
    7.   `orddate`
    8.  END)


  • MarkSnodgrass
    MarkSnodgrass Coach

    @ozarkram I would double check your formula because what I wrote doesn't have "isnull" in it, it has "ifnull".

    **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 :Yes I have checked the same..I have used only ifnull

Categories