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

How to add months that has no data?

JoRao
8 - Asteroid

Hello - 

 

I am trying to force add Jan and Feb to the month field. For example, Group XYZ only has data for the month of March, but I need to show 0's for Jan and Feb. or if a group has data for Jan and Feb but nothing in March, I will need to march in but show 0's. How do I accomplish this? Also, I want to be able to scale it to any other group that may show up in Apr but has no values in Jan, Feb or Mar.

 

Group NameMonthABCDEF
ABCFEB00-300-3
ABCJAN01-31-2-3
ABCMAR00-80-2-10
DEFFEB07-3004
DEFJAN032-412-6-13
DEFMAR02-80-1-7
XYZMAR200002


Thanks a lot in advance!

 

Best,

Jo

6 REPLIES 6
TrevorY
Alteryx
Alteryx

I've constructed the attached workflow that shows how you might go about creating a check to make sure that the correct number of months is included. This workflow uses your exact text to make sure that Feb, Jan, and Mar are all present for each group. If you wanted to scale up and add April (or subsequent months), you can simply alter the filter to 4 months and add April in the text input. I hope this helps!

JoRao
8 - Asteroid

Perfect! Worked like a charm!! Thank you so very much!!

 

Best,

Jo

JoRao
8 - Asteroid

I have another question related to this post: Once I added the month field, how would I populate the Group ID and Group Category? I tried using the Multi-row formula with an If then statement, but that did not work. Thanks!

 

Group NameMonthGroup IDGroup CategoryABCDEF
ABCFEB1234Office Supplies00-300-3
ABCJAN1234Office Supplies01-31-2-3
ABCMAR1234Office Supplies00-80-2-10
DEFFEB4567Paper07-3004
DEFJAN4567Paper032-412-6-13
DEFMAR4567Paper02-80-1-7
XYZFEBNullNullNullNullNullNullNullNull
XYZJANNUllNullNullNullNullNullNullNull
XYZMAR8910Furniture200002
GHIJAN1112Electronics34-5123
GHIFEB1112Electronics123456
GHIMARNullNullNullNullNullNullNullNull
TrevorY
Alteryx
Alteryx

I would create a key in the form of a text input that has each group name and its corresponding group ID and group category. I would then join that key data in at the end of this workflow to make sure that these fields are joined in for each group name (join on group name). You can also use a formula tool to write an if then statement for the group ID and group category fields, separately. 

JoRao
8 - Asteroid

Thanks again @TrevorY!!

TrevorY
Alteryx
Alteryx

No problem!

Labels