Card from Outer Join Two Datasets ETL

Shumilex

I'm trying to join two datasets, each dataset has a date field, how can I join the date field to get one Date Column that I can use on the X Axis, but will not remove any rows from the other columns. 

 

1st dataset has: created date, complaints cases, end date, customer type

2nd dataset has: balance date, loan in books, loyalty level, and merchant id

 

I want one dataflow with, Date, complaints cases, end date, loyalty level, loan in books, and merchant id. But the "Date" column would be a combination of the "Created Date" and "Balance Date" fields. 

 

I am trying to get a card that would have: 

X Axis: Date (combined column)

Y Axis: (Deferral % of Loans) - I will do a beast mode Complaints Cases/Loans in Books (Line)

Series: Loans in Books. (Bar Chart) 

 

Please let me know!  Thanks 

 

GrantSmith

Hi @Shumilex 

 

You can use a Magic ETL to join the two dates together using a Join tile based on the two dates to get a resulting dataset. After the join you can use a Select Columns tile to only select the columns you want and to rename them appropriately.

 

You could also do this with a DataSet View if you're familiar with those which has some additional benefits of being updated when the underlying dataset is updated instantly and not counting against your row limit if that's a concern.

 

 

To clarify - is it possible that your dataset would have a date in one dataset and not the other or are all dates represented in both datasets?

 

If it's possible that a date would appear in one dataset and not the other then you'd want to use a FULL OUTER join on your two dates then using an ETL 2.0 formula tile you can COALESCE each column with 0 and COALESCE your two date fields together into a new Date column.

 

 

Another option would be to format your data differently such that you have a date, a metric name and the value for each data set and then UNION them together.

 

| Date | Metric Name | Value |

|---|---|---|

| 1/1/2021 | Complaint Cases | 500 |

| 1/2/2021 | Complaint Cases | 100 |

| 1/3/2021 | Complaint Cases | 50 |

 

| Date | Metric Name | Value |

|---|---|---|

| 1/1/2021 | Loans in Books  | 500 |

| 1/2/2021 | Loans in Books  | 100 |

| 1/3/2021 | Loans in Books  | 50 |

 

Then using a DataFusion or a Magic ETL with an append tile perform the union.

You can then write your beast mode to look at the metric name to get the value performing a SUM on the value.