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

[Claje · Accepted solution]

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!

[David-Carnes]

@monish_chandra 
I came back to post my solution and see that @Claje beat me to it.  I took a slightly different path, in which I converted your string date to an actual date. I then used the Generate Rows tool to produce similar output to the previous solution.