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.
image
Sheet 2.
image
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)
image
Nesting readers (Cross joiner or joiner didn´t work)
image

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

BR
-N

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

Thanks,
Sanket

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,
Kaz

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.
image

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

-N

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

2 Likes

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

-N

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,
Kaz

4 Likes

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

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

-N

4 Likes

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