Matching a value between two other values in another table

[Dean Udsen]

Hi all,

I have two data tables that I'm trying to match values between based simply on a depth in the first table falling between two depths in the second table.

The first table is Log data that has an identifier (UWI), a depth and a value(GR_MWD)

The second table has the same identifier (UWI), a stage number and a start and end depth for thatstage.

There is nothing relating the two tables together other than the UWI identifier.

I'm trying to answer the question, at depth x of the log, it aligns with stage y. In other words, using the depth in the first table, find where it lands between the depths in the second table

For example in Table 1 (Log Data) there is a depth of 2999.20 with a value of 102.50

In the second table (Stage Details) I can see that depth 2999.20 "lands" between the depths at Stage 30 (2999.00 and 3040.80)

So the stage 30, has a GR log curve vcalue of 102.50. or alternatively you could say the log value of 102.50 aligns with stage 30.

Is there a way to match records with some sort of between clause or perhaps an expression in a calculated column that would add the corresponding stage number to the log table

.dxp file with data is attached.

 

Thanks!

Dean

[Khushboo Rabadia]

You can achieve this in Spotfire if your Spotfire version is above 10.1 as approach which I have used uses Filter transformation.

Basically you can insert columns depth start, depth end and stage columns in log table matching on uwI column and left outer join.

Then add transformation to creating bunch of columns which will help you decide which rows to retain and which are duplicates.

Then use filter transformation to rest of unwanted rows.

Atlast, you will have only rows which you had initially but now new columns for Stage, depth start and depth end.

I have shown this approach in attached dxp.