Question

Join table to use as stringsearcher regex

  • 18 June 2019
  • 1 reply
  • 4 views

Badge

Hello, I have sql query from our db (sql creator) containing records as e.g.

Id; Source, CreationDate

1; WTT Australian Notice 123, 20190312

2; German Notice 12GG3 and VTS, 20190412

3; VMS and Canadian Notice 53T, 20190312

4; Canadian 78UU, 20190312

I want to use a regex stringseracher for the source attribute and assign a value based on the search result.

I have an excel table that contains the regex I want to search for in the db records and the value I want to assign e.g.

Regex; Country

 

Australian (Notices|Notice); AUSTRALIA

 

Brazilian (Notices|Notice); BRAZIL

 

Canadian|Canada (Notices|Notice); CANADA

How can I use this table as my regex string?

My output should look like

Id; Source, CreationDate; Country

1; WTT Australian Notice 123, 20190312; AUSTRALIA

2; German Notice 12GG3 and VTS, 20190412; null

3; VMS and Canadian Notice 53T, 20190312; CANADA

4; Canadian 78UU, 20190312; null

Thanks for your help!


1 reply

Userlevel 2
Badge +17

A possible way I can think of is:

  1. ListBilder: Populate every pair of Regex and Country into a list.
  2. FeatureMerger: Merge the list to every record from the source table unconditionally (set an identical value e.g. 1 to the Join On for both Requesor and Supplier).
  3. ListExploder: Explode the list on the Merged features.
  4. StringSerther: Filter features matching the Regex.
  5. FeatureMerger: Merge the Matched features to the original source features, using Id as Join On attribute. Matched features will be output via the Merged port, not matched features will be output via the UnmergedRequertor port.

The workflow looks this.

0684Q00000ArJnaQAF.png

Addition: This regex might fit to your requirement, for CANADA.

(Canadian|Canada) (Notices|Notice)

Reply