How to Reorder the Values of a Matillion Grid Variable

Data

How to Reorder the Values of a Matillion Grid Variable

Often when using grid variables in Matillion, the order of the values is important. For example, you could be iterating through the values of a grid variable sequentially using the Grid Iterator component and need the iterations to complete in a desired order. You may even have populated your grid variable using the Query Result to Grid component, which is not guaranteed to order the values in your desired structure due to how Matillion structures its SQL.

If you have run into such a situation and need to reorder the values in a grid variable, you can do so with a simple Python script.

Ordinal Position Indexing in Python

Before looking at the script, it is important to know the ordinal position of the field within the grid variable you wish to order by, as this is fed into the sort operation.

Consider the following grid variable, where I have indicated Python’s ordinal position index for each field. This indexing starts at 0 (zero), not 1, which is a standard practice among programming languages:

If desired, a more detailed explanation can be found here.

Python Component to Reorder a Grid Variable

As promised, here is the Python script. The first section allows you to consider a few variables, such as the name of your grid variable and whether to sort the values in ascending or descending order. For our example, we set the field index to be 0 (zero), so the sort will take place on the ID field from the example screenshot above:

##################################################
## Input variables

# Determine the name of the grid variable which will be sorted
matillion_grid_variable = 'MY_GRID_VARIABLE'

# Determine the ordinal position index of the field that will be used for the sort.
# Remember to start your index at 0, not 1
field_index = 0

# Determine whether the sort should be descending (True) or ascending (False)
sort_descending = True

##################################################
## Main process 

# Retrieve the current, unsorted values from the grid variable
py_my_grid_variable = context.getGridVariable(matillion_grid_variable)

# Only perform the script if the grid variable is not empty
if py_my_grid_variable is not None:
  
  # Sort the list by the desired element in the list.
  py_my_grid_variable.sort(key=lambda x: x[field_index], reverse = sort_descending)

  # Update the Matillion grid variable with the new, sorted list
  context.updateGridVariable(matillion_grid_variable, py_my_grid_variable)

If you place this piece of code into a Python Script component, you can read, sort and update the values of a grid variable.

I hope you find this short and sweet post useful. It may be the shortest post I’ve ever written!

More About the Author

Chris Hastie

Data Architect
A Definitive Guide to Creating Python UDTFs in Snowflake Using Snowpark Snowpark for Python is the name for the new Python functionality integration that Snowflake has recently developed. At the Snowflake ...
A Definitive Guide to Creating Python UDTFs Directly within the Snowflake User Interface Snowpark for Python is the name for the new Python functionality integration that Snowflake has recently developed. At the Snowflake ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072