unexpected result from joining to ETL data flows

Hi,

I am not sure what is going on with the join, the inner join adds only rows that have a matching identifier in both datasets, I dont know the definition by heart, but it might just be that it matches multiple times because it is a constant... 1k x 1k = 1m.

But there is an easier way than matching on your additional text string. You can just reformat the datetime  column you have into a date column - in SQL this works with DATE_FORMAT(), in ETL this would be "Set column type" - you can find instructions here. If that doesnt work as intended, you can go with a bunch of "Date operations" and "Combine columns" that gives you a date column as well. Bear in mind that both date columns need to have the same format too.

You might want to group (like sum, also on the above knowledge page) your values by date (not datetime) before you join the sets, but that depends on what your data actually contains.

HTH JHL

Any chance you could provide some example values of the two datasets you're wanting to join as well as what you're wanting to happen?

We should be able to provide more help with some more detail.

Sincerely,

ValiantSpur

So if you're joining on date, you'll need to do something to take care of the "last week" data that is reporting back every 10 minutes. If you remove the timestamp and then join on date, you'll end up with every date in the last week being matched to every other row with that day (so instead of a 1 to 1 join you end up with a 1 to many, which is probably causing your high row count).

1st question: Do you only need 1 row per day? (I'll assume you do with my answer below)

2nd question: Is every date in your range accounted for? (ie, do both datasets have the exact same dates returned. If one returns weekends but not another then it will affect the results. I'll assume they are they same dates between them)

Step 1: It sounds like you did this right, but I would begin by setting both dates to a Date type.

Step 2: You need to group on  your date field (If there are 200 records for Feb 13, we need to average the value for that day)

Here's a quick SQL transform you could follow to do this for each dataset:

SELECT `Date`, AVG(`Value`)
FROM Dataset
GROUP BY `Date`

If your run both datasets through the grouping, you should end up with 1 record per day for each.

Step 3: Still assuming you have the same dates between both. You can then do a LEFT JOIN to combine the two

SELECT a.`Date`, a.`Value` AS aValue, b.`Value` AS bValue
FROM dataset1 AS a LEFT JOIN dataset2 AS b
ON a.`Date` = b.`Date`

That should give you the result it sounds like you're looking for.

Let me know if I'm wrong on one of my assumptions or if you need me to clarify anything.

Hope this helps,

ValiantSpur

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.