Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

GROUP BY month and JOIN to categorize items by month

monish_chandra
8 - Asteroid

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. 

 

MonthCategory
02/2018ICU
03/2018CHF
03/2018Ventilator

 

 

2 REPLIES 2
Claje
14 - Magnetar

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
12 - Quasar

@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.

 

monish_chandra.PNG

Labels