Comparing Differing Values Across Different Tables

[Gabriel Tatman]

Hello,

Lets say that I have two tables: T1has a column that records all of the measured depth values associated with a well name, and T2 has a column of depth values that describe a characteristic (such as fluid level). I am trying to color all of the measured depth values in a T1 visualization that are less than the max depth in T2 that is associated with the selected well name.

These depth values do not match, and the columns are different lengths, but each table has a column that matches well identifiers. I would imagine that there would be some way to insert a calculated column in T1 with a boolean valuefrom whether measured depth is less than or greater than the max depth value (well by well) from T2....

Where do I start

Regards,

Gabriel

 

Edit: Since I am only trying to reference 1 value (max fluid depth) from T2, would this be easier to do by creating a property that updates with the selected well to create a calculated column

[Jason Joslin 3]

I've done something similar to this before. First, see if this will suit your needs:

 

It's a quick and easy way to show the characteristics as horizontal lines in another data tables visualisation.

Steps:

 

Create a relationship between your two data tables on 'Well Name'.

Set up an XY chart.

In the chart properties add a horizontal line based on a data table.

 

The second way is more advanced. It relies pivoting the characteristics data table and joining it to the original well depth mearsurements data table. Then it will be easy to compare each depth value to the characteristic to see if it is higher or lower:

 

Then you can produce the following, or do even more interesting calculations:

 

 

Attached in the project file (Spotfire version 7.12) for you to look at.

[Gabriel Tatman]

Hi Jason,

 

Thank you very much for your reply, I believe this is certainly a step in the right direction! It looks like the second method you mentioned better matches what I am trying to do here. I am still running into a bit of an issue when trying to add the columns to table 1 with a pivot. I have attached a more specific example of the data tables I am working with, along with an example of the desired outcome. The desired end result is a 3D Scatter Plot generated from the first data table that colors or marks all values below a maximum depth at a specfic time or time interval. 

 

The characteristics do change with date, and I would like to be able to include a filter option in the end result, but if it were easier to pull the most recent characteristic value then this would still be useful. 

 

Before your response, I was able to add some of the depth characteristic values by selecting Insert>Columns and matching the two tables, but doing so deleted the calculated columns that were previously in place for Table 1 for some reason, so that method may be a dead end. 

 

 

 

[[{"fid":"141911","view_mode":"default","fields":{"format":"default"},"type":"media","field_deltas":{"1":{"format":"default"}},"link_text":"sample.xlsx","attributes":{"class":"media-element file-default","data-delta":"1"}}]]

 

 

 

[[{"fid":"141926","view_mode":"default","fields":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false},"type":"media","field_deltas":{"2":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false}},"attributes":{"class":"media-element file-default","data-delta":"2"}}]]

 

 

 

 

[Jason Joslin 3]

I think if you just allow the join (default left outer), it will replicate all the rows of the original table for each date. This may seem overwhelming, but it's actually useful as I think you'll then have what you need.

 

Step 1:

 

[[{"fid":"141966","view_mode":"default","fields":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false},"type":"media","field_deltas":{"1":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false}},"attributes":{"class":"media-element file-default","data-delta":"1"}}]]

 

Step 2:

 

[[{"fid":"141971","view_mode":"default","fields":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false},"type":"media","field_deltas":{"2":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false}},"attributes":{"class":"media-element file-default","data-delta":"2"}}]]

 

Then I made something like this:

 

[[{"fid":"141976","view_mode":"default","fields":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false},"type":"media","field_deltas":{"3":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false}},"attributes":{"class":"media-element file-default","data-delta":"3"}}]]

 

But there are loads of ways you could look at it.

[Gabriel Tatman]

Hi Jason,

 

Thanks again for your input. While matchng via default left header was one of the first methods I had tried (and it does work for importing the data), it deletes all of my calculated columns for some reason. I had originally hoped that there would be another way. 

 

As always, I appreciate your advice. Please let me know if you can think of any other way around this.

 

Gabriel

[Pushkar Hagawane]

I have compared the row count of twotables in a calculated value by ensuring that both the tables have at least one column having same name, data type.

Also to ensure that rowcount should be displayed wven if data is not there in both the tables I created sample data table through clipboard and created column in that table same with the same name and data type as in both the tables.

See if this works for you!