Align different value rows with Foreing Key

Hello Colleagues.

This time I´m looking for help because I´m getting very confused hahaha

Let´s give some context:
I´m dealing with a huge dataset (Is an excel with 11 sheets).
This sheets can be related with a kind of Foreing key (to recognize the data throught the 11 sheets), It also has different time stamp records for the same foreing key along the pages.
Sheet 1.
Sheet 2.
The thing I need here is to “Align” or “Group” in the same table the “Date & Time” rows of all the 11 sheets to make some calcs with those dates.

And the way I´m reading the excel files is this.

By loop (I got all the 11 sheets data)
Nesting readers (Cross joiner or joiner didn´t work)

I´ll appreciate any help with my problem, I´m so confused, and Sorry if is too extense this post :sweat_smile:


Hi @ttsk8der ,
Could you show us a table with the desired output?


Hi @ttsk8der
Expected output table could help, as @sanket_2012 wrote.
In the meantime I’ve noticed your input tables have different column names. If you could have columns:

  • FK for foreign key
  • Date & Time
  • Sheet # as additional column. You could use Constant Value Column node with iteration number or iteration number + 1 to start with sheet #1

then your ‘By loop’ approach should work.

Happy KNIMEing,

1 Like

If they have the same column structure, then you will want to use the Concatenate node to combine them into a single dataset.

1 Like

Hello! @sanket_2012 , @Kazimierz, @iCFO : D. Thank you all for your replies.

Of course I´m attaching the expected output table. 1Date, 2Date, 3Date and so on. Are the result of operate every date for the same foreing key that is present throught the dataset sheets.

Answering @Kazimierz question,. Yes! All the sheets have the same structure and column names, the difference is they have different date or time records.

And @iCFO ´s solution, it works, but, it´s not what the loop is doing? :thinking::thinking: I got confused here.

Again, thank you all for your time and replies


It looks like you may want to pivot the concatenated table on the foreign key field.


Hello :smiley: @iCFO .

Haven´t tried it yet. I think is the perfect moment because I have the complete dataset thanks to @Kazimierz constant value Idea


I would agree with @iCFO’s suggestion to use Pivot node. The rough sequence would be:

  • Table Row to Variable Loop Start
  • Excel Reader
  • Constant Value Column
  • Loop End
  • Pivot with:
    • FK as Group
    • dates as Pivot
    • First as Aggregation method (First should be enough - to be checked with real data)

Good luck,


Thank you guys.
pivoting was the perfect node for the task.

I really appreciate your help. Thank and good luck!1



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