Join monthly data to daily data

Hello and thank you for any assistance.

Please see node annotation in image below and the associated workflow I have attached.

Straight forward/probably simple - I am just learning sort of.

image

Here is what annotation says (in case my image above does not come through):

Table A is data by day of each month.

Table B is data by each month (not daily).

How can I join these so that I get data only once for each year/month match, not for every day?


Thanks again for any assistance.

Help.knwf (140.3 KB)

Could you be more specific about the desired outcome? :wink: Would it be like this?

Sorry to be vague.
Below (and attached) is my concept of how I THINK (not sure) it should result from the join.

In the end, these are the only rows that the Business Plan would join with. Only one day each Period even if the Period does not yet have sales data.

The entire table would look like the attached Excel item.
Join Result.xlsx (80.0 KB)

Thanks very much for any assistance.

Use this node to extract month on the full dates.

Use a formula on the month table to ensure a match for join.

I like to use the “month number” and year columns for joins like this.

Thank you for your reply. However, in my join (example workflow I attached), I am already attached on month, day and year. None the less, I attempted to use the node you suggested and I get an error. Either I have done something wrong using this node, did not understand your reply properly or perhaps this is not a valid solution. Thanks again for helping.

image

Your date column needs to be formatted as a Date data type. It is likely a String. Try using the String to Date node first.

Hi and many thanks.

I am not using a date column. Please see image below for the contents of the tables and what I am trying to join.

Keep in mind… I don’t want the join on every day of every period for 2024. Only one instance of the join.

Intended outcome of join:

If you want to join the month table with the day table And keep the detail of each day in the rows, then you would join on year and period For both tables.

If you no longer want the day detail of the data, and only want to join the summary of each month, then you would want to use the “group by” node to summarize the day detail table to get monthly averages before joining it with the other monthly table. (I think this is what you’re going for from looking at your expected output)

It might be easier if you upload a workflow with some sample data in it.

Yes and thank you. simple workflow is in this reply. really appreciate any help. I believe what you are saying in your most current post is where I want to be. Many thanks for sticking with me here. Learning. Thx.

Help.knwf (140.3 KB)

1 Like

Help.knwf (152.0 KB)

See if this is what you were aiming for. I used “Last” in Group By for the day detail, and summed by month and attribute.

It splits the joins to 3 separate outputs for easier review to see what matched and didn’t from each table.

1 Like

Very good! Thank you. I believe this is exactly what I wanted (see image below).

Much appreciated and will mark as correct/best answer.

Thank you again.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.