ETL, Date replacement, can't work out how

Reply
Highlighted
Green Belt

ETL, Date replacement, can't work out how

I have a data set like the one below, via ETL I new to create a column with the data on the Date 2 column if available, if not, with data from Date 1 column if available, otherwise leave it blank.

It is dead easy to do in Beastmode but as I need to column for the Date Grain the new column needs to be created in the Dataset.

Every thing I have tried leads to ETLs that fail to even run.

 

 

Date.png


Accepted Solutions
AS Major Blue Belt
Major Blue Belt

Re: ETL, Date replacement, can't work out how

I think a SQL dataflow would probably better suit your needs here, given the lack of a function in Magic ETL for something like this.

 

SELECT

`Field 1`

,`Date 1`

,`Date 2`

,CASE WHEN `Date 2` IS NOT NULL THEN `Date 2`

WHEN `Date 2` IS NULL and `Date 1` IS NOT NULL THEN `Date 1`

ELSE NULL END AS `Date 3`

FROM 

data

 

Something along those lines

Aaron
MajorDomo @ Merit Medical

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

All Replies
AS Major Blue Belt
Major Blue Belt

Re: ETL, Date replacement, can't work out how

I think a SQL dataflow would probably better suit your needs here, given the lack of a function in Magic ETL for something like this.

 

SELECT

`Field 1`

,`Date 1`

,`Date 2`

,CASE WHEN `Date 2` IS NOT NULL THEN `Date 2`

WHEN `Date 2` IS NULL and `Date 1` IS NOT NULL THEN `Date 1`

ELSE NULL END AS `Date 3`

FROM 

data

 

Something along those lines

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
New Dojo experience is here! Here are some quick tips, we would also love your feedback. Click here!