Beast mode to extract the date based on a pattern

ozarkram · 2022-06-08T20:47:19+00:00

There was an error rendering this rich post.

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:

can you please provide some insights here..

Sample sheet.xlsx

Find more posts tagged with

Accepted answers

MarkSnodgrass

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)

GrantSmith

@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

All comments

MarkSnodgrass

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)

ozarkram

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

@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.

ozarkram

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

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)

GrantSmith

@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.

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

ozarkram

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

ozarkram

@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

@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

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)

MarkSnodgrass

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

ozarkram

Hi @MarkSnodgrass :Yes I have checked the same..I have used only ifnull