topic How to fix missing value in Alteryx Designer Desktop Discussions

How to fix missing value

BlakeGriffin2093 — Tue, 12 Mar 2019 15:53:16 GMT

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

Re: How to fix missing value

ChrisTX — Tue, 12 Mar 2019 15:57:12 GMT

Try the Summarize tool

Can you post sample input and desired output?

Re: How to fix missing value

estherb47 — Tue, 12 Mar 2019 17:45:35 GMT

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.

Cheers!
Esther

Re: How to fix missing value

BlakeGriffin2093 — Tue, 12 Mar 2019 18:26:08 GMT

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

Date	Price/ Ton
01/01/2011	243
02/01/2011	246.53
03/01/2011	250.04
04/01/2011	257.72
05/01/2011	243.05
06/01/2011	245.41
10/01/2011	251.56
11/01/2011	249.42
12/01/2011	251.70
13/01/2011	246.00
14/01/2011	248.42
15/01/2011	249.44
16/01/2011	255.99
17/01/2011	249.28
18/01/2011	258.76
19/01/2011	246.41
20/01/2011	256.28
21/01/2011	253.69

Re: How to fix missing value

estherb47 — Wed, 13 Mar 2019 16:00:33 GMT

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

Re: How to fix missing value

BlakeGriffin2093 — Sun, 17 Mar 2019 14:21:56 GMT

Thank for your helping. Have a nice day