How do I merge columns?

kelly
kelly Member
in Archive

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? 

Comments

  • PEAKS_valleys
    PEAKS_valleys Member

    Hey @kelly - 

     

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

  • Ops
    Ops Member

    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
    jaeW_at_Onyx Coach

    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories