Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Min Max and Match, and Group

LincolnMike
8 - Asteroid

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.

 workflow.JPG

9 REPLIES 9
cmcclellan
13 - Pulsar

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
8 - Asteroid

Hi cmc,

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

 

error.JPG

LincolnMike
8 - Asteroid

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.

expected2.JPG

 

danrh
13 - Pulsar

This is also doable using Multi-Row Formula tools:

image.png

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
8 - Asteroid

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.

expected3.JPG

cmcclellan
13 - Pulsar

@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
13 - Pulsar

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:

 

image.png

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
8 - Asteroid

Thanks for the help Chris.

LincolnMike
8 - Asteroid

IMG_6363.jpg I just pushed it.  Thanks Dan.

Labels