Min Max and Match, and Group

Hi,

per the following image and attached yxmd (version 10.6), I am trying to combine data for the output below, but I have one hiccup:

I need the associated Reading from the Max Date, and I need the Date associated to the Minimum Reading. I only get the Max Date with Max Reading and Min Date with Min Reading. Please help.

MinAndMaxAndMatch.yxmd

Common Use Cases

Accepted answers

danrh

This is also doable using Multi-Row Formula tools:

I'm sorting by date, copying values down, then sorting by reading and copying values down again. Then a group by/min/max/count in the Summarize tool gets the desired output.

Hope it helps!

solution.yxmd

danrh

So if I understand correctly, if the min reading and the max reading are the same, you want to grab the min date --- is that correct? You should be able to do this by adding another level to your second Sort tool:

This puts the min date above the other dates in the event that the readings are the same, and hence copies the earlier date down.

All comments

cmcclellan

Tricky I was looking at the max/min reading initially but you want the reading for the mix/max date.

I couldn't find what you've done wrong, but I think mine is more compact and gives the right results.

MinAndMaxAndMatch CM.yxmd

LincolnMike

Hi cmc,

I can't open that yxmd you uploaded. I have version Alteryx 10.6. Could you save at a lower version?

LincolnMike

Hi Chris,

I was able to open the file by editing the XML and changing the version to 10.5, but the result is still not quite there. In the image below for expected, I need the

Max Date ~ associated reading

Date ~ associated Minimum Reading.

danrh

This is also doable using Multi-Row Formula tools:

I'm sorting by date, copying values down, then sorting by reading and copying values down again. Then a group by/min/max/count in the Summarize tool gets the desired output.

Hope it helps!

solution.yxmd

LincolnMike

thanks Dan and Chris for your help.

I used Dan's and it worked for the sample and when I applied it to my 300,000 row data set. I did find an issue:

If Min readings happens to be same as the max reading, it takes the date associated to the Max Reading.

The users asked how that was possible if there are more than one reading taken.

I added a couple more rows of data (unit 111) to display my extra dilemma.

solution_addData.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703