Hi, I am trying to do check if data is correct in one of my tables compared to another one. To do that, I thought about merging/joining the two tables based on columns and conditions. The common column between the two tables is the City Code (postcode). Then I need to compare landmark values, which represent the location of objects in space. I managed to join them based on the City Code column but I don’t manage to do the landmark values comparison yet. More context:My actual data is fields of grass along roads. Each section of road belongs to a city (City Code) and is separated into categories of landmarks. For example a road within city 1 is separated into groups of landmarks ranging from 1 to 1.5; 2 to 3; etc.; and the field X is situated at a specific landmark value 2.5.So the field should be in the landmark range 2 to 3 because its landmark value is higher than 2. The tables: The first table has 3 columns and links city code and landmarks to a unique ID: City code: the city postcode Landmark_start: the starting point for the landmark range ID: a corresponding unique ID The second table is my actual custom data where each line represents a field of grass. City_code: same as above Landmark_value: landmark value of the field ID: “should be” the same unique ID from above based on the City Code and the Landmark_object ---The problem is the unique IDs are not always correct and so I need a way to check and compare them. The basic logic will involve matching the City Code from the two tables (I’ve already done this with a FeatureJoiner), and then comparing the landmark values to the landmark ranges to make sure they match correctly (I’ve not found how to do this). How could I add a joining condition based on the data in the two tables? Something like Landmark_value > Landmark_startI didn’t find how to do that in the FeatureJoiner transformer. In the FeatureMerger transformer, a conditionnal value is possible but it seems to be only by table and not mixing data from the two tables. Hope my data tables and context is understable, I tried to describe as much as possible.

Joining two tables based on comparison of values

Hi,

I am trying to do check if data is correct in one of my tables compared to another one. To do that, I thought about merging/joining the two tables based on columns and conditions.

The common column between the two tables is the City Code (postcode). Then I need to compare landmark values, which represent the location of objects in space.

I managed to join them based on the City Code column but I don’t manage to do the landmark values comparison yet.

More context:

My actual data is fields of grass along roads. Each section of road belongs to a city (City Code) and is separated into categories of landmarks. For example a road within city 1 is separated into groups of landmarks ranging from 1 to 1.5; 2 to 3; etc.; and the field X is situated at a specific landmark value 2.5.

So the field should be in the landmark range 2 to 3 because its landmark value is higher than 2.

The tables:

The first table has 3 columns and links city code and landmarks to a unique ID:

City code: the city postcode
Landmark_start: the starting point for the landmark range
ID: a corresponding unique ID

The second table is my actual custom data where each line represents a field of grass.

City_code: same as above
Landmark_value: landmark value of the field
ID: “should be” the same unique ID from above based on the City Code and the Landmark_object

---

The problem is the unique IDs are not always correct and so I need a way to check and compare them. The basic logic will involve matching the City Code from the two tables (I’ve already done this with a FeatureJoiner), and then comparing the landmark values to the landmark ranges to make sure they match correctly (I’ve not found how to do this).

How could I add a joining condition based on the data in the two tables? Something like Landmark_value > Landmark_start

I didn’t find how to do that in the FeatureJoiner transformer. In the FeatureMerger transformer, a conditionnal value is possible but it seems to be only by table and not mixing data from the two tables.

Hope my data tables and context is understable, I tried to describe as much as possible.

Page 1 / 1

Can you use the change or update detector and use the unique id as the key, and then look for changes in the other values to see where they deviate?

I have been looking into it a bit but I’m not sure how it would work. In the changedetector, the original and revised data seemed to be more or less the same type of data. Here I would like to check that, in my fields data, the unique ID corresponds to the one it should have, based on the city code and the landmark values stored in the first table (having all the unique ID for each city).

I also found the Matcher transformer, which seems very interesting, but I didn’t manage to make it work yet. It seems possible to compare two attributes in the same table, so I could do a comparison of my two ID columns, after joining the two tables based on the City Code.

More informations:
Sorry, I should have written down more clearly that my field data at this point doesn’t contain geometry. The only information I have related to geometry is the landmark values column.

Sorry im not quite following. you cant join things with greater than statements. could you join them and the test for greater than to see where it fail that rule for the landmark values?

Thanks, I think that’s the best way to test if the value is right or not. If it’s wrong, I think I will have to find the geometry in a previous file to join correclty and get the unique ID based on the landmark values. Thanks for your help!

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded