Hi,
I have two tables and I need to merge based on BldName
Table 1:
BldNameConstruction StatusldABCCompDEFblOngoingBCdbldPlanned
Table 2:
BldNameTypeABCResidentialDEFCommercialBCdIndustrial
Is there any solution to achieve this task.
Thanks is advance
+17
It's hard to think of a solution unless you specifically explain what rules are applied to determine if two building names from the two tables match.
It's hard to think of a solution unless you specifically explain what rules are applied to determine if two building names from the two tables match.
Sorry I was not explained well.
I have two table and both are having "BldName" field.In second table "BldName" fields values contains in first table "BldName" field value based on that need to join the table to get Type for first table.
example : IdABC = ABC
Expected result is like shown below.
BldNameConstruction StatusTypeldABCCompResidentialDEFblOngoingCommercialBCdbldPlannedIndustrial
+17
Not clear.
Every building name in the Table 1 seems to be equal to a string consisting of a building name in the Table 2 and a specific prefix or a specific suffix in the Table 2. Is it the rule?
If so, are only these three patterns possible? Or are there any other patterns?
Not clear.
Every building name in the Table 1 seems to be equal to a string consisting of a building name in the Table 2 and a specific prefix or a specific suffix in the Table 2. Is it the rule?
If so, are only these three patterns possible? Or are there any other patterns?
There is no specific prefix and suffix.
Example 01:
First table :
Building name = "Beach 01 House"
Second table:
Building name = "Beach 01"
Example 01:
First table :
Building name = "Green 01 Home"
Second table:
Building name = "Green 01"
above two example there are common name is "Beach 01" and "Green 01"
based on this common string need to join two tables.
+17
Two possible ways.
Use the InlineQuerier with this SQL statement.
select
a."BldName",
a."Construction Status",
b."Type"
from "table1" as a
inner join "table2" as b
on a."BldName" like '%'||b."BldName"||'%'
+16
I see 2 potential options:
1) If the tables are not too big:
Prefix both table1 attrbutes and table 2 attributes (t1 and t2) using BulkAttributeRenamer
Use FeatureMerger (in 2018 FeatureJoiner) to merge unconditionally (1 = 1)
Use the Tester to find t1.BldName Contains y2.BldName.
This should get you the required result, but takes long if the tables grow.
2) Use the InlineQuerier transformer to build a query like:
Select t1.BldName, t1."Constructions status", t2.Type
From "table 1" t1, "table 2" t2
Where t1.BldName like '%'||t2.BldName||'%'
That too should get the same result.
Hope this helps.
I wonder if using an InlineQuerier could work, maybe using a join like this:
SELECT
Table1.BldName,
Table1.ConstructionStatus,
Table2.Type
FROM Table1
JOIN Table2
ON Table1.BldName LIKE '%' + Table2.BldName + '%'I can't test it right now for the exact syntax, but perhaps using this principle, it could work.
+32
I see 2 potential options:
1) If the tables are not too big:
Prefix both table1 attrbutes and table 2 attributes (t1 and t2) using BulkAttributeRenamer
Use FeatureMerger (in 2018 FeatureJoiner) to merge unconditionally (1 = 1)
Use the Tester to find t1.BldName Contains y2.BldName.
This should get you the required result, but takes long if the tables grow.
2) Use the InlineQuerier transformer to build a query like:
Select t1.BldName, t1."Constructions status", t2.Type
From "table 1" t1, "table 2" t2
Where t1.BldName like '%'||t2.BldName||'%'
That too should get the same result.
Hope this helps.
Can confirm option 2 works. Learned something new today!
Would prefer the use of the InlineQuerier over prefixing and featuremerging both tables due to performance.
What I miss are the FeatureMergers UnmergedRequestor and UnuserSupplier ports for total control. Is this doable with SQL?
+32
Two possible ways.
Use the InlineQuerier with this SQL statement.
select
a."BldName",
a."Construction Status",
b."Type"
from "table1" as a
inner join "table2" as b
on a."BldName" like '%'||b."BldName"||'%'
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.