Jump to content

Punctual to interval data interconnection

Yannick Santerre
Go to solution Solved by Gaia Paolini,

Recommended Posts


I am trying to match a set of discrete points (i.e. "Property A" for each punctual "Depth") existing in one "Table_1", with another set of data existing as intervals (i.e. "Property "B" for each interval, in between a "Top_depth" column and a "Bottom_depth" column) existing in another "Table_2".

I haven't found a way to create a new calculated column containing the corresponding "property B" into the "Table_1" (i.e. when "Top_depth" > "Depth" > "Bottom_depth" for each "Property A").

Could you help me please?

Thank yourenditionDownload.png.a68d127c9921fbcbbaff171923c7e944.png

Link to comment
Share on other sites

  • Solution

I replied to a similar question a few months ago, got no feedback so I don't know if and which solution worked.


You could do it via a data function (R or Python) or you could try purely in Spotfire.

The Spotfire method only involves Spotfire joins and calculated columns. It might generate a lot of rows initially.

The idea is to do a complete cartesian join of the two tables and then filter in only the depths that are between top and bottom.

1 - via the data canvas, add a transformation = Calculate New Column to your table 1, called e.g. [forJoin] with a single value, any value, e.g. 1. This column will be used for the join

1 - do the same for table2

3 - create a new table based on your original table 1.

 go to + and choose 'Other' then select the original data table: choose 'add as new data table'.

 this creates a new table based on the original data. 

4- add columns to this table, coming from table 2. Join on [forJoin], join settings=Full Outer Join. This creates all the row combinations.

 save the joined table as 'always new table', as it would refresh every time you update the original table.

5- still on the data canvas, add a filter as ([depth]>=[top]) and ([depth]<=[bottom])

 this will keep only the rows with depth between top and bottom.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...