sequence number.xlsx (10.1 KB)

Hi,

How do I generate this sequence number? Thanks!

Hi @Hoctiep12A

Welcome to KNIME Forum. See this wf Generate sequence numbers.knwf (24.8 KB)

gr. Hans

Hello @Hoctiep12A and welcome to the KNIME forum

Your challenge description is very short, and it doesnât refer to your use case. @HansS workflow generates the alpha-numeric sequence from scratch, stepping on iteration column.

If you wan to to apply the sequence to a pre-existing data (and avoiding loops); then you will have to start with a âMath Formulaâ node for the numeric part:

$new column$ ==

```
ceil( ($$ROWINDEX$$ + 1) / 4)
```

After that you can generate your alphabetical sequence with another âMath Formulaâ node (rank node optional):

$alpha_numbers$ ==

```
mod( $$ROWINDEX$$ , 4) + 1
```

Convert the $alpha_numbers$ to characters with a Rule Engine:

$alpha_numbers$ ==

```
$rank$ = 1 => "A"
$rank$ = 2 => "B"
$rank$ = 3 => "C"
$rank$ = 4 => "D"
```

And finally combine numeric and alphabetical indexes with âString Manipulationâ node (after $new column$ String to number):

$target_sequence$

```
join($new column$, $alpha_numbers$)
```

BR

Hi @Hoctiep12A , I agree @gonhaddock 's comment and I agree that when posting a question such as this it is better to give a little more description to the problem, and specifics of your use case as this can lead to very different solutions and some will be more efficient than others. I also recommend explaining *what* the sequence is (even if it may appear obvious to you).

In this case we are to assume that the sequence is simply sets of 4 rows with ascending numeric as the first part with and rotating the letters A to D as the second part. I do enjoy puzzles, but âŚ

Anyway, similar to the math formula and rule engine solution, this can also be achieved (if you have existing data against which the sequence is to be added) using String Manipulation, and then you donât need a second node to deal with Strings.

The sequence does require a âmoduloâ calculation, which String Manipulation is lacking, but this can easily be achieved using basic mathsâŚ

```
join(
string(toInt($$ROWINDEX$$ / 4 ) + 1 ),
substr("ABCD", $$ROWINDEX$$ - toInt($$ROWINDEX$$ / 4 ) * 4 ,1)
)
```

So here, the equivalent of the Math Formulaâs âceilâ function

`ceil( ($$ROWINDEX$$ + 1 ) / 4)`

is

`toInt($$ROWINDEX$$ / 4 ) + 1`

and the generalised conversion for `MOD(n, m)`

is `n - toInt(n / m) * m`

so

`mod( $$ROWINDEX$$ , 4) + 1`

is replaced by

`$$ROWINDEX$$ - toInt($$ROWINDEX$$ / 4) * 4`

(we donât add the 1 because weâll be using it in the substr function which uses zero for the first character of the string)

The substr function replaces the need for the Rule Engine by taking the nth item from the string âABCDâ and the join function concatenates the two.

kudos, master @takbb !!

for sharing a single node solution. Tons of learnings in a single piece of code.

BR

Hi @gonhaddock , thank you for the comment, and glad you found it of interest.

I do find that the mathematical capabilities of String Manipulation are often overlooked. Its name is a a misnomer because it can handle (to an extent) numeric calculations too, even being able to return them via the toInt, toLong and toDouble functions.

I find myself in many cases using String Manipulation for numeric manipulations because (in my view) the math formula node is far too restrictive by virtue of it being incapable of handling non-numerics - not even in an if() test. In contrast, String Manipulation can perform calculations, conditional tests (if you know how!) and obviously manipulate strings too. For that reason, there are actually very few occasions I use the Math Formula node. (i.e. only if I need one of the more involved mathematic operations, which is not often ).

Where possible my preference is *no-code* over *low-code* over *code*, but when needed, I find String Manipulation much more versatile than its name and documentation would generally have us believe!

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