Punctual to interval data interconnection

[Yannick Santerre]

Hi,

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 you

[Gaia Paolini]

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

https://community.spotfire.com/s/question/0D54z00007pNO8OCAW/how-to-calculate-pairwise-euclidean-distances-between-datasets-on-heatmap

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.

[Yannick Santerre]

Thank you very much for the help Gaia, greatly appreciated.

It is working and I am now able to display what I wanted.

Shame there is no easier direct way to do this simple thing.

Many thanks again Gaia.

[Gaia Paolini]

I found an idea on the Idea Portal for 'Greater/lesser than Join Types (like SQL)' that would help in the future.