How do I merge columns?

kelly

I am trying to get my columns to be consildated. I have duplicates after joining my data together. How am I suppose to do this? 

PEAKS_valleys

Hey @kelly - 

 

How are you joining? Are you using the Magic ETL or MySQL dataflow? 

Ops

Hi PEAKS_valleys or anyone else reading this as well,

 

I know this one is an oldie, but I'm curious to know if it can be done in ETL ?

 

In my case, I have deliveries going out multiple times a day, but doesn't happen everyday.

 

Eg.

Mon 29-Jun

Mon 29-Jun

Mon 29-Jun

Tue 30-Jun

Fri  03-Jul

Fri  03-Ju

Fri  03-Jul

 

But I need to account for the missing days, where I'd like to add new Column: Full Calendar.

 

Full Calendar   Despatch Date   Deliveries

Mon 29-Jun        Mon 29-Jun        ABC0001

Mon 29-Jun        Mon 29-Jun        ABC0002

Mon 29-Jun        Mon 29-Jun        ABC0003

Tue 30-Jun         Tue 30-Jun        ABC0004

Wed 01-Jul

Thu 02-Jul

Fri  03-Jul           Fri  03-Jul        ABC0005

Fri  03-Jul           Fri  03-Jul        ABC0006

Fri  03-Jul           Fri  03-Jul        ABC0007

 

I'm hoping to do this in ETL if possible, as I'm no good with SQL but planning to get into it later on, once I get some time. Cheers!

jaeW_at_Onyx

You can accomplis this by setting up a LEFT JOIN where the left table has a list of every date, and the right table has your transactional data.

 

I would however encourage you to evaluate what value this adds to your data model / analysis because I don't think modeling the data this way adds much value. 

 

If i had to guess you're planning to calculate ratios like "how many days did i not have activity" or "average sales per day". if that's the case, instead oF JOINing the data, I'd advise UNION / APPENDING the data such that your date dimension is an activity" baseline" and then align the date columns.  Then you can use a CASE WHEN activity Type = transaction" / case when activity type = "baseline" to calculate ratios.