Union tool sorting data automatically

[fmvizcaino]

Hello community,

As I'm answering questions here in community, I came across this, did a quick test and the rows are being sorted automatically.

Am I crazy or it wasn't suppose to be happening? Simple workflow attached.

Best,

Fernando Vizcaino

[BrandonB · Accepted solution]

The Union tool does not have a sort built in. I believe that has to do with how you are splitting the data in the filter tool and bringing it back together. See the workflow below:

 

 

 

[BrandonB]

There must be some logic that it maintains order of record ID behind the scenes when splitting from a filter tool. 

[echuong1]

To sort the records (or to maintain the order in your case), you can use the Output Order option in the configuration. From there you can specify the order of the output records. 

[GiuseppeC · Accepted solution]

Interesting one, @fmvizcaino!

I ran a similar test to @BrandonB's and agree that the behaviour that you are getting is not due to an actual sorting in the Union tool (see below). When the same data comes into the Union from 2 separate streams the output of the Union is not "sorted". 

My guess is that in your particular configuration the output of the Union refers to the same memory location containing the data as it enters the Filter tool, so what you see is not data that it first split, then unioned and sorted, but just the data as the filter tool reads it.

Interesting one indeed!

Giuseppe

[fmvizcaino]

@GiuseppeC , @echuong1 , @BrandonB thank you for your answers!!!

Seems like that @GiuseppeC!

In that case, I need to go after that in order to understand what happening in memory.

Thanks everyone!!

[fmvizcaino]

@danilang ,

would you be able to share your opinion about this topic, please?

[danilang · Accepted solution]

Hi @fmvizcaino 

caveat emptor

From the results of tests I've tried, the Alteryx folks are correct.  If you don't specify an output order, then Alteryx does the minimum amount of work necessary to combine the records in a Union.  Splitting the records using a filter tool seems to flag the records as opposed separating them in memory, so when you recombine them using a union, the flags are erased and the records are in the pre-Filter order.  This makes sense when you consider that the engine is designed by some very clever people to as fast as possible

There are ways that you can trick the engine, though.  In the topmost stream of your workflow, I added a Sort on RecordID desc after the filter.  Even though there is only one record in this stream, the Sort algorithm plays with memory enough that the union, the "4" record is after the others

This is probably related to the concept of a blocking tool.  A blocking tool stops the flow of records through it until all the upstream tools have completed.  A Filter tool is non-blocking since it can process the records as they're available, flag them and send them downstream.  A Sort tool is blocking, since there's no guarantee that the sort is complete until all the records have been received.  Blocking tools probably all change the in-memory representation of the records.  

I learned something today as well.

Dan

[fmvizcaino]

It made perfectly sense!!!

Thank you for your answer @danilang!