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?
Hey @kelly -
How are you joining? Are you using the Magic ETL or MySQL dataflow?
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
Tue 30-Jun
Fri 03-Jul
Fri 03-Ju
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!
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.
