Skip to main content
  • EN

Hello There

we are looking to update a table with the new attribute called layer the value will be populated by searching a keyword into the attribute ( Type ) that should contain the keyword in the lookup table

there is w way to do it by using the conditional value on the attribute value and writing all the key word there, but this will be a long task and not simple to maintain. since data is so big

we are looking to use the existing excel to maintain the lockup and then search only the keyword into the value of the attribute

Any suggestions?

Table



Dig_lcn
Type
Status


125
Electric network
open


126
New Phone line
open


127
sewer conduite intalation
closed


128
Mv electric ntework
closed


129
sewer renwell conduct
open

if the attribute value containe the keyword in the lockup table



Lockup


ID
layer


1
Electric


2
Phone


3
sewer

then we get this Final result



Dig_lcn
Type
Status
ID
Layer


125
Electric network
open
1
Electric


126
New Phone line
open
2
Phone


127
sewer conduite intalation
closed
3
sewer


128
Mv electric ntework
closed
1
Electric


129
sewer renwell conduct
closed
3
sewer

Hi @boubcher, a possible way is, split the Type string into individual keywords and save them into a list attribute (AttributeSplitter), explode the list (ListExploder), then merge the look up table (FeatureMerger), as in:

0684Q00000ArJYTQA3.png

Alternatively, the InlineQuerier with this SQL statement can also be used.

select a.*, b.*
from "source" as a inner join "lookup" as b
on ' '||a."Type"||' ' like '% '||b.layer||' %'

Hi @boubcher, a possible way is, split the Type string into individual keywords and save them into a list attribute (AttributeSplitter), explode the list (ListExploder), then merge the look up table (FeatureMerger), as in:

0684Q00000ArJYTQA3.png

Alternatively, the InlineQuerier with this SQL statement can also be used.

select a.*, b.*
from "source" as a inner join "lookup" as b
on ' '||a."Type"||' ' like '% '||b.layer||' %'

 

@takachi

 

you are the best

Reply


Community Stats

30,159
Posts
114,480
Replies
37,807
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.