How do I merge columns?

White Belt

How do I merge columns?

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? 

Green Belt

Hey @kelly - 


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

Yellow Belt
Yellow Belt

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.



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!

Tags (2)
Black Belt

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.

Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
New & Improved Dojo: Get excited for a new feel and additional features in the online community.

Site will be down February 4-5th for maintenance.

Launch coming February 8th!