Yesterday, I received two separate questions from clients about how to create a table with only unique observations of an identifier. In this case, they only wanted one row per well, which would allow them to export the desired view to other users. Here, we will show you how to do so in a temporary visualization as well as a separate table.
First, I will show how to create a virtual table with just one row per well. I say “virtual” because I’ll only be altering the table in a visualization, not the underlying master table itself. To get started, I created a calculated column using the following:
If(Rank(RowId(),”asc”,[well name])=Min(Rank(RowId(),”asc”,[well name])),True)
This expression leverages the Row ID, ranks rows per well, and gives a True value to the first well. While in our case, the first date gets the True, really all that matters is there is one per well. We can now create a table with only one observation per well by limiting the data. Right-click the visualization > Properties > Data > Limit data using expression and use the following expression:
[Unique Wells] = True
Above, we see a visualization of the table with only one row per well, which could allow for input columns next to each well, an export of the table, a clear marking table, or many other use cases.
If you want to change the underlying table itself, follow the above steps, then mark the Unique Table. Right-click > Invert. Then Right-click > Delete. You will be left with only the unique values.
Jason is a Data Scientist at Ruths.ai with a master’s degree in Predictive Analytics and Data Science from Northwestern University. He has experience with a multitude of machine learning techniques such as Random Forest, Neural Nets, and Support Vector Machines. With a previous Master’s in Creative Writing, Jason is a fervent believer in the Oxford comma.