Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
i'm trying to clean up a date string field that has an inconsistent format.
2019-01-01, 01/01/2019 etc..
in previous ETL tool that i use, i will write up something like this:
IF Contains([Service Dt],"-") THEN [Service Dt] ELSEIF Contains([Service Dt],"/") THEN Regex_Replace([Service Dt],"(\d{1,2})(/)(\d{1,2})(/)(\d{4})","$5-$1-$3") ELSE Null() ENDIF
How do i write this formula in TMap? or is there a better way of doing it?
Thank you in advance.
@Schrute_Farms ,you can use the below function in output of enable/Disable filter Expression. the below function will return true or false.here mydate should be string.
TalendDate.isDate(row7.mydate,"yyyy-MM-dd")==true ?row7.mydate:null
@Schrute_Farms ,for multiple condition you need to write below way.
TalendDate.isDate(row7.mydate,"yyyy-MM-dd")==true || TalendDate.isDate(row7.mydate,"dd/MM/yyyy")==true ?row7.mydate:null
@Schrute_Farms as I understand, what you want is an expression to unify the output format.
Here is what my suggestion:
TalendDate.isDate(row29.value, "yyyy-MM-dd") ? row29.value : TalendDate.isDate(row29.value, "dd/MM/yyyy") ? row29.value.substring(6) + "-" + row29.value.substring(3, 5) + "-" + row29.value.substring(0, 2) : "Not a valid date"