Not sure which join to use

HowDoIDomo

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?

ColemenWilson

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.

ColemenWilson

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.

HowDoIDomo

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

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.

HowDoIDomo

Thank you!