Calculate order sequence by client and p

Dear All,

I would like to create a column of “order sequence” based on the client and purchased date, meaning if the same person purchased more than one transaction on the same date, it will be counted as the same order sequence.

For instance, client A purchased two transactions on 1/1/2018, then third transaction on 2/1/2018 and forth transaction on 3/1/2018. In this case, I would like the column to become 1 for 1st transaction, 1 for 2nd transaction (due to the same date as 1st transaction), 2 for 3rd transaction and 3 for 4th transaction.

Could anyone teach me how can I create such column in KNIME?

I have attached the expected results I would like to create, thanks and look forward to hearing from you.

Client Purchased Date Order sequence (would like to be created by KNIME)
A 1/1/2018 1
A 1/1/2018 1
A 2/1/2018 2
A 3/1/2018 3
A 4/1/2018 4
B 1/1/2018 1
B 1/1/2018 1
B 2/1/2018 2
B 3/1/2018 3
B 3/1/2018 3
B 3/1/2018 3
C 1/1/2018 1
C 2/1/2018 2
C 2/1/2018 2
D 1/1/2018 1
D 1/1/2018 1
E 2/1/2018 1
F 2/1/2018 1
G 2/1/2018 1
G 2/1/2018 1
G 3/1/2018 2
G 3/1/2018 2
G 4/1/2018 3
G 4/1/2018 3
G 4/1/2018 3
G 5/1/2018 4

Try to read your data or put it in a table creator node.
Use Rule Engine with TRUE => $Order Sequence$ to add new column.
Then Pivot Node group by Client and Purchse Order pivot on Order Sequence and aggregate
on new column with function First.
Join original table with pivot result (first output port) on Client and Purchase Date.

2 Likes

Dear izaychik63,

you are amazing, it works perfectly.

Thank you very much

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