Grouping by first character or characters

I currently have a table with reference codes in. The rows need to be grouped individually by the first character or characters.

For example, codes can be GATT01, FXTER03, BJUT02.

They need to be grouped like: G for General, FX for FXX, B for Council, U for Utility etc.

What is the best way of doing this in Knime? I’ve tried the rule-based row filter/splitter but would have to use 6 of the nodes to achieve the desired result for each code I have to group by.

Could I create a lookup table or something somehow?

Hi @nickdavies07 and welcome to the KNIME forum,

If I have got it right, you want to have a column which has values like “General”, “FXX”, “Council”, … and group the rows by these values. You have values like “FX” (2 characters) and I guess there may be even more cases with even more number of characters representing different values.

My suggestion is to use a dictionary to solve this. Here I have an example workflow for you:

rule_engine_dictionary.knwf (18.8 KB)

The bottom Table Creator here is the dictionary. You can build a complete one regarding your values. Then by using the String Manipulation node, rules are created and finally the Rule Engine (Dictionary) node will produce a new column which has the complete form of the values where any abbreviation you have defined exists at the beginning of the code.

Then you can group the rows using a GroupBy node based on this new column.

:blush:

This is perfect!

Thanks for your help @armingrudd. This’ll actually be useful for other tasks I’m working on.

:sunglasses:

1 Like

I’m glad I could help. Does it solve your current issue as well?

It does - I’ve marked your answer as the solution.

Thanks!

1 Like

:blush:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.