Generate sequence numbers

[Hoctiep12A]

sequence number.xlsx (10.1 KB)
Hi,
How do I generate this sequence number? Thanks!

[HansS]

Hi @Hoctiep12A

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

gr. Hans

[gonhaddock]

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

[takbb]

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.

[gonhaddock]

kudos, master @takbb !!
for sharing a single node solution. Tons of learnings in a single piece of code.
BR

[takbb]

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!