Not sure which join to use

HowDoIDomo
HowDoIDomo Contributor
edited April 2024 in Magic ETL

I am trying to link 2 datasets to show scheduled appointments and web traffic in the same visit. The goal is to show what, if any, correlation there may be for traffic to specific ad campaigns and appointments for their related services.

When I use datafusion or etl join, it gets messy because the only matching data I have is dates. So, I end up with something like this:

Date |URL visited | Appointment

1-1-2024|Oncology URL| General Medicine Appt

1-2-2024 | Bariatric URL | Prenatal Appt

So, filtering on one column causes me to lose the data I need for another column

If I filter, then do append rows, I get:

Date |URL visited | Appointment Date| Appointment

1-1-2024|Oncology URL| NULL | NULL

NULL | NULL | 1-1-2024 | Oncology Appointment

I have all the data I need pre-filtered. But, I can't graph by date because I'll lose half the data.

Is there another dataflow type or ETL tile that will allow me to keep all of the data, but have 1 date column?

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    You can rename the fields in MagicETL. You just have 1 date from each datasource right?

    So for example, if in Dataset 1 the date name is Date1 and in Dataset 2 the date name is Date2 you could rename the date field in each to be New Date. Then union the data together and Domo will match up the date columns as a single column.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited April 2024

    Rename the 2 date fields, or create a new date field, in MagicETL to have the exact same name and then append the data from the two sources. Then you'll have a single date field and wont lose data when filtering.

    If I solved your problem, please select "yes" above

  • HowDoIDomo
    HowDoIDomo Contributor

    I can't rename the date fields since they come from their respective connectors. I'm trying to create a new date field in MagicETL, but not sure how to get the dates from both columns into one

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    You can rename the fields in MagicETL. You just have 1 date from each datasource right?

    So for example, if in Dataset 1 the date name is Date1 and in Dataset 2 the date name is Date2 you could rename the date field in each to be New Date. Then union the data together and Domo will match up the date columns as a single column.

    If I solved your problem, please select "yes" above

  • HowDoIDomo
    HowDoIDomo Contributor

    Thank you!

Categories