How do I combine and average two rows?

[leewa007]

I have the above information and need to combine (for example) Record 1229 and 1230 into one row and the _value needs to be the average of the two values. How do I combine and average the two?

[Luke_C]

Hi @leewa007 

You could use the summarize tool to group by fields such as Postal Code or Station and then get the average value. However if you truly need to get the average of the specific rows you mentioned there doesn't seem to be any common fields that link those two fields, you'd need to add a field to group them by.

[PanPP]

Hi @leewa007 

Looking at the data, you can use a summarize tool to group by fields such as Postal code and get the average value of the column you are looking for.

There is an issue since there are multiple postal codes or stations that would overlap into multiple records.

Please let us know if you have any other questions, hope this helps.

[leewa007]

• 

  Would the fact that the URL has the zip code work? 

IF so how could I combine them with the URL sip code and average the value but also taking into consideration the date 

[PanPP]

Hi @leewa007 

You can parse out the URL text into zip code, date, and group by zip code/date in the summarize tool and average the column value you are trying to find.

You can use text to column tool to parse out the tool or use regex in order to parse out the tool 

https://help.alteryx.com/20223/designer/text-columns-tool

https://help.alteryx.com/20223/designer/regex-tool

Hope this helps, if it does please like this post and if it helps resolve your problem, mark it as a solution. If you have any other questions, please let us know.

[Luke_C]

Hi @leewa007 

Sure that works, but there's more than just the two records with the same URL. Can you clarify your requirement? Are you looking to get the average by zip code or just the two distinct rows you mentioned?

[leewa007]

The URL is pulling information from the Weather page online and we are supposed to get the max temperature per zip code and were provided 3 zip codes. However, the issue is that for one of the zip codes we received two stations, and in order to get the information we need to present we just need one number per zip code. In our case, this is the reason why we need to combine the two stations for one zip code and average the value instead of having to values. 

[Luke_C]

Hi @leewa007 

Look into the summarize tool. Sounds like you can group by the zip and station, and get the 'max' value. 

https://help.alteryx.com/20223/designer/summarize-tool

[AkhtarPawa]

1. Select the cell where you want to put the combined data.

2. Type is equal and select the first cell you want to combine.

3. Type & and use quotation marks with a space enclosed.

4. Check Out this Post how to win in this Battle game

5. Select the next cell you want to combine and press enter. An example formula might be equal to A2& &B2.

[leewa007]

icon What icon am I supposed to use because everyone mentions the summarize icon but that will only allow me to do one. It does not allow me to combine two different rows or add them together.