Hi there, I've got a complex string field that looks like this: No.String Fields1LED Daytime Running Lights2LED Daytime Running Lights with Adaptive Control3Daytime Running Lights with Fog Lights What I wish to do is to group the string fields into separate clusters (Group A / Group B / Group C) based on a complex rule which may need to write some regular expression if necessary. And a key-words sheet is also available to feed into the algorithm. Contains(Key Words 1)Contains(Key Words 2)Not Contains(Key Words 3)Group Daytime Running LightsFog LightsALEDDaytime Running Lights BLEDAdaptive Control C If similar string fields are added, what we need to do is to update the key words sheet without modifying the regular expression. (The Sting fields have millions of records so it is not practical to define a fixed group.) Say if someone write "Daytime Running Lights" as "DRL", we just add DRL to the key words list so that the string fields can be grouped accordingly. The find replace tool does not solve my problem because it uses a simple contains function. Any help is appreciated, Thanks!

Conditional Append with complex rules

Hi there,

I've got a complex string field that looks like this:

No.	String Fields
1	LED Daytime Running Lights
2	LED Daytime Running Lights with Adaptive Control
3	Daytime Running Lights with Fog Lights

What I wish to do is to group the string fields into separate clusters (Group A / Group B / Group C) based on a complex rule which may need to write some regular expression if necessary.

And a key-words sheet is also available to feed into the algorithm.

Contains(Key Words 1)	Contains(Key Words 2)	Not Contains(Key Words 3)	Group
	Daytime Running Lights	Fog Lights	A
LED	Daytime Running Lights		B
LED	Adaptive Control		C

If similar string fields are added, what we need to do is to update the key words sheet without modifying the regular expression. (The Sting fields have millions of records so it is not practical to define a fixed group.)

Say if someone write "Daytime Running Lights" as "DRL", we just add DRL to the key words list so that the string fields can be grouped accordingly.

The find replace tool does not solve my problem because it uses a simple contains function.

Any help is appreciated,

Thanks!

Help

Accepted answers

danilang

Hi @steve_tang

This was a tricky one.

The left half of the WF, up to the three vertically aligned joins, prepares the source and look-up data. The three joins match on each of the key word columns. This is why I asked If you had only three of these. If you had a varying number, the logic would have been more complex, probably involving a macro or two. The right part of the macro performs the 1 and 2 but Not 3 logic and cleans up the results to be displayed as