bringing data from vertical to horizontal after join

Dear Experts,

I have to join 2 tables outbound and inbound bringing matches from vertical to horizontal

Inbound is grouped by column inbound.key

Inbound.key is joined to outbound.key.

Table inbound = original state

Table outbound = original state

Table inbound after join = result state

for key = 10:

outbound.a1 = inbound.a from 1st match in inbound

outbound.b1 = inbound b

outbound.c1 = inbound c

outbound.d1 = inbound d

outbound.e1 = inbound e

outbound.a2 = inbound.a from 2nd match in inbound

outbound.b2 = inbound b

outbound.c2 = inbound c

outbound.d2 = inbound d

outbound.e2 = inbound e

outbound.a3 = inbound.a from 3rd match in inbound

outbound.b3 = inbound b

outbound.c3 = inbound c

outbound.d3 = inbound d

outbound.e3 = inbound e

for key = 20:

outbound.a1 = inbound.a from 1st match in inbound

outbound.b1 = inbound b

outbound.c1 = inbound c

outbound.d1 = inbound d

outbound.e1 = inbound e

outbound.a2 = inbound.a from 2nd match in inbound

outbound.b2 = inbound b

outbound.c2 = inbound c

outbound.d2 = inbound d

outbound.e2 = inbound e

Remark: There can be up to 10 rows for the same key in table inbound.

How to realize Table outbound after join?

Any idea highly appreciated.

Thanks for help!

BR,

Heinz

You’re more likely to get help if you share your current workflow and data. That would give potential helpers something to work with.

Pls find attached workflow:

test.knwf (19.4 KB)

You can see table inbound with data in several rows per each key (key 10 has 2 rows)

Is there a chance to manipulate outbound or inbound so I have only one row per key and the columns a and b (now in separate rows) in a1, b1, a2, b2, …

image

In original you can find the data as they are in inbound.
In result you can see the result
(data have to be transformed from horizontal to vertical, multiple rows to multiple columns)

I used colours in the screenshot to show data source and destination.

Hope that description is easier to understand.

BR,

Heinz

You need to add a column which identifies the instance of the row, eg a row count by RowID (eg 1, 2). Then you can Pivot on this column. To add the said column, Group Loop Start over key β†’ Math (Rowindex+1) β†’ Loop End.

2 Likes

Thank you very much for your help; you made my day!

test_simecek.knwf (40.7 KB)
Hello Heinz,

I send you my improvement in your workflow for generate result table.
It is another idea how to solve your problem.

Karel Simecek