Create a Spotfire Table with Only Unique Observations / Wells

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.

Good luck!

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.

2 thoughts on “Create a Spotfire Table with Only Unique Observations / Wells

  1. Shubham Reply

    Hi Jason

    Thanks for the above article.
    I know the below query is little off the topic, but looking for some help and ideas around the same.

    I am in situation where i need to dynamically remove few columns from XLS data source while loading it as data table in Spotfire Analysis file. I tried looking into Transformations, but no luck. Happy to share more details, if needed.

    Any leads/ideas will be really appreciated. Thanks

    Regards
    Shubs

  2. Jason May Reply

    Shubs,

    I think you can just load the full file, then go to Data > Column Properties (or Edit > Column Properties before Spotfire X). From there, you can select a column and delete. I believe when you open the file the next time, it will not include those columns you deleted.

    Good luck,
    Jason

Leave a Reply

Your email address will not be published. Required fields are marked *