KNIME vlookup among rows and columns

Hello,

Currently I have a table like this

WORK GROUP ID,…,MONTH,…,.CLIENT ID,…,NUMBER OF TRANSACTIONS

Work group 1,…,.Month 1,…,Client 1,…,.25
Work group 2,…,Month 1,…,Client 4,…,.1
Work group 4,…,Month 1,…,Client 12,…,…56
Work group 1,…,Month 1,…,Client 2,…,…27
Work group 1,…,Month 1,…,Client 5,…,…,…34
Work group 1,…,Month 2,…,Client 2,…,…28
Work group 3,…,Month 2,…,Client 7.,…,…35

The table above has many work groups distributed in 12 months (sevaral work groups are NOT present in the 12 months), each work group has its own client (several clients per work group) and I aslo have the number of transactions that the work group X attended to Client Y in month Z. There are more than 100K entries.

However, i would like to rearrange the table to look like this

WORK GROUP ID…MONTH…CLIENT 1… CLEINT 2 …CLEINT 3 … CLIENT N

Work group 1…Month 1… 25…27…0…0
Work group 2…Month 1 … 0… 0…0…0
Work group 3…

I already know how to use the group by node to obtain this:

WORK GROUP ID MONTH CLIENT TRANS
Work Group 1 Month 1 Client 1 25
Work Group 1 Month 1 Client 2 27
Work Group 1 Month 1 Client 3 0
.
.
.
Work Group 1 Month 2 Client 1 14
Work Group 1 Month 2 Client 3 3
.
.
.
Work Group 2 Month 1 Client 4 15
Work Group 2 Month 1 Client 3 67

But How can I make an arrange like the one I mentioned?

Hi,

See attached workflow, hope it helps. Basically, it’s just about pivoting, other nodes there are aimed
to get correct client column order like Client 1, Client 2, etc. at the final output.

Martin K.

vlookup-among-rows-and-columns.knwf (11.0 KB)

1 Like