We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

How to fix missing value

BlakeGriffin2093
7 - Meteor

how can i fulfill the missing value by using max (or min) value of each month in each year 

5 REPLIES 5
ChrisTX
15 - Aurora

Try the Summarize tool

 

Can you post sample input and desired output?

estherb47
15 - Aurora
15 - Aurora

Hi @BlakeGriffin2093 

You can use a summarize tool, grouping on both year and month, and then choosing the Max or Min on the summary field.

Then join those values back into the original data set. A formula tool replaces the nulls with their values.
image.png
Cheers!
Esther

BlakeGriffin2093
7 - Meteor

but if days are missing value and i want fulfill all of these days with max value days of each month in each year. I try your solution but it seems doesn't work

DatePrice/ Ton
01/01/2011243
02/01/2011246.53
03/01/2011250.04
04/01/2011257.72
05/01/2011243.05
06/01/2011245.41
10/01/2011251.56
11/01/2011249.42
12/01/2011251.70
13/01/2011246.00
14/01/2011248.42
15/01/2011249.44
16/01/2011255.99
17/01/2011249.28
18/01/2011258.76
19/01/2011246.41
20/01/2011256.28
21/01/2011253.69
estherb47
15 - Aurora
15 - Aurora

Hi @BlakeGriffin2093 


First, go ahead and make sure your Date column is being treated as a date, and not as string (based on the format in your example, it looks like string). That can be accomplished using the DateTimeParse tool.

 

Your data would just need a touch more prep before summarizing, where we pull out the month and year from the date column using another DateTime parse, summarize, and impute back with a join on that month and year field.


Sorry I'm unable to attach pictures today. My computer is wonky at the moment.

Cheers!

Esther

BlakeGriffin2093
7 - Meteor

Thank for your helping. Have a nice day

Labels