Create a column by selecting a data point from multiple columns from ETL or beastmode

user06979

Hi all, 

 

I am trying to create a new column from multiple columns in my dataset to extract the data point that I need. Let's just say that there are three columns:A,B, and C that I am looking at to see which data point I need to use.I want all the data I use to be in the newly created column Z. If there is a name in column A, but nothing in B and C, then I want to extract data from column A and put it in column Z. If there is a name in column B, then I want to extract a name in column B, instead of A. The same with column C. C takes priority over B, B takes priority over A. 

 

Is it possible to do this in ETL? or beast mode?

 

Thank you so much

MarkSnodgrass

Yes, you can do this in Magic ETL or MySQL. I am going to assume you want to do this in the Magic ETL. If your data only has values in only of those 3 columns, you should try using the combine columns tile. If your data has values in 2 out of the 3 or all 3 and you want to prefer column c when there are multiple values, you will want to make use of the filter rows tile and use it multiple times to split off your data and then append it back at the end with the new column z. Let me know if you need more help with breaking out your input dataset to multiple filters and bringing it back together again.

MarkSnodgrass

You can do this pretty easily in a beast mode. If an empty column is truly null, you can use multiple IFNULL statements, starting with column C since that is your preferred column to use. 

IFNULL(`ColumnC`,IFNULL(`ColumnB`,`ColumnA`))

If you don't have null values, but there are filled with empty spaces, you could build a case statement that still prioritizes column C over B and A, like this:

(CASE WHEN TRIM(`ColumnC`) = '' then 
 /* nothing in column c, so check column b */
	(CASE when TRIM(`ColumnB`) = '' then
     /* nothing in column b, so use column a */
     `ColumnB`
     ELSE
     /* there is something in column b, so use it */
     `ColumnA`
     END
    )
ELSE 
/* there is something in column c, so use it */
 `ColumnC`
END)

user06979

Thanks. That helps.

 

I know that I asked whether this can be done in beast mode or ETL, but I think I need this done in ETL because I'd like to join this dataset with a different dataset with that newly created column as an identifying column to merge the rows. Would this be possible?

 

Thanks in advance.  

MarkSnodgrass

Yes, you can do this in Magic ETL or MySQL. I am going to assume you want to do this in the Magic ETL. If your data only has values in only of those 3 columns, you should try using the combine columns tile. If your data has values in 2 out of the 3 or all 3 and you want to prefer column c when there are multiple values, you will want to make use of the filter rows tile and use it multiple times to split off your data and then append it back at the end with the new column z. Let me know if you need more help with breaking out your input dataset to multiple filters and bringing it back together again.