Min Max and Match, and Group

[LincolnMike]

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.

 

[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.

[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 · Accepted solution]

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!

[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.

[cmcclellan]

---

@LincolnMikewrote:

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.

---

I know you have the solution (& it's a better solution), but that's what I mentioned in my reply - I was doing the max/min reading but you were saying max/min date which is why I changed to do that :)  It's quite easy to change the workflow to go ff max/min reading

[danrh · Accepted solution]

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.

[LincolnMike]

Thanks for the help Chris.

[LincolnMike]

 I just pushed it.  Thanks Dan.