Data Augmentation

[ManuelRodrigues]

Hi,

Hope you are well!

I have an Alteryx question for you that I can’t seem to find an answer to in either the online help or forums.

A lot of the data we use in Excel requires a unique field on which to do lookups and to keep calculation times as low as possible we use the version of vlookup which requires the data lookup field to be sorted A-Z to return the correct value (true as last attribute). In Alteryx, I have found that neither of the two available options on the sort tool (either use dictionary or don’t use dictionary) gives the same sort order as the Excel A-Z sort and we therefore have to run an additional sort step on data sets created in Alteryx before it will work in Excel binary lookup.

Do you have any ideas or am I missing something obvious?

The attached workflow and example data demonstrates – drop the two files in the same folder and use the workflow.

You can see that neither sort option matches how Excel sorts/requires the data. Alteryx seems to sort punctuation after numbers whereas Excel sorts punctuation before numbers :

The example data is sorted in Excel A-Z order :

Alteryx Dictionary Sort does this to it :

And non-dictionary sort does this :

[messi007]

@ManuelRodrigues,

You have to convert the Excel A-Z Order to double using the select tool and then Sort.

Attached the workflow,

Regards

[ManuelRodrigues]

@messi007 still didnt work with the data set I attached.

[messi007]

@ManuelRodrigues,

Attached the new update :

Regards,

[Ben_H · Accepted solution]

Hi @ManuelRodrigues,

The only way I could get it to sort in the same way as excel was to pad out the central section of your lookup with 0's then sort in non dictionary order.

You can see the sort field I created on the right here -

I padded the central section to 15 characters but you might need to change this depending on your data.

Regards,

Ben