GROUP BY month and JOIN to categorize items by month

monish_chandra

Hello,

I'm trying to join two data files based on a field called Category. One of the files has a date. I want to find out the missing category for every month. Is there a way to join based on every month? like in SQL : JOIN ON Category groupby month(date).

Attached is a sample workflow. The expected results are as shown below. 

Month	Category
02/2018	ICU
03/2018	CHF
03/2018	Ventilator

JoinByMonth.yxmd

Claje

Hi,

Take a look at the attached workflow.

To accomplish this, we need to create a list of all expected Month/Category results.  To do this I used a Formula to create the Month/Year field you are expecting, followed by a Summarize tool to get the distinct list of months.  I then appended this to our list of categories, to create a record for every category and every month.

Finally, I expanded on your JOIN requirements to include the new "Month/Year" field we created.  The "R" output of this join has the missing fields you are expecting.

Let me know if you have questions!

claje_JoinByMonth.yxmd