Merge cell, transpose and add request date

Hello, can you help me with attached workflow?
Everyweek i will receive raw data rolling for 7 weeks

Merge and Transpose.xlsx (21.9 KB)

Hi,
Shouldn’t be there a workflow? Or you meant the Excel file?
And what is you question exactly?
Best,
Armin

Hello @armingrudd

i meant the excel file already included the raw data and the output i need, but i don’t know the workflow, complicated for a new knime user as me. thanks for help.

I checked the data set, but I couldn’t understand what your question is.
Would you please explain it to me?

Hello @armingrudd

In raw data file/original file/input, the week number(column) is in merge cell, may i know how to transpose that to row not column?

So you want to add the excluded column in the second sheet (wk2) be back (from wk1) and then do the transposing part.
Let me know if I’m right, So I can start working on it.
Armin

Morning @armingrudd,

Background:

  1. tab sheet “Original File Received in wk1”: raw data excel file i received separately from email every week. This is a sample of raw data i received in 2019 wk1.

  2. tab sheet “Original File Received in wk2”: raw data excel file i received separately from email every week. This is a sample of raw data i received in 2019 wk2.

  3. Output needed": this is the format i need but i don’t know how to generate from knime

Difficulty encountered/action needed:

  1. Transpose from column G-AA week number row 2 such as 51, 52, 1, 2…(which are merge cells) in tab sheet “Original File Received in wk1”, to rows see example in tab sheet “Output needed” column D “week”.
    Value is then grouped by “Base Forecast” and “GR (SU)”, and no longer displaying week by week horizontally, but vertically as the week is transpose to row now. See example in “Output needed” column F and G.

  2. Add Request Date in Column E according to week number in column D. See example in tab sheet “Output needed”. The Request Date = first day of the week number indicated in column D. And Request Date is a newly added column which you cannot find in raw data i.e. “Original File Received in wk1”

  3. The reason why raw data “Original File Received in wk2” is provided:
    The raw data file is rolling 7 weeks, so in “original file received in wk1” i receive result of wk51-wk5, see example “Original File Received in wk1” column G-AA row 2. The “Original File Received in wk1” file is published to me by email in wk1 2019. In “original file received in wk2”, i received result of wk52-6 in column G-AA row 2.The “Original File Received in wk2” file is published to me by email in wk2 2019.
    For the output i generated in wk1 2019 in tab sheet “Output needed”, then column D week will be wk51-5 in tab sheet “Output needed” because this is what shown in “Original File Received in wk1” column G-AA,
    then in wk2 2019 when i generate “Output needed”, i will need wk51 and result to KEEP in the Output column D (which is not in “original file received in wk2”), while updating wk52-wk6 in column D in tab sheet “Output needed” result according to “Original File Received in wk2”. Then when i received raw data in wk3, KEEP result of wk51 and 52, and updating result of wk1-7 in Output needed.
    So continue this process for each week.

  4. say current week is week 2 of 2019, but the “Original File received in wk2” provide data up to wk6, see tab sheet “original file received in wk2” column Y, I only need week number and its data up to week 2 (current week) in “Output needed” tab sheet see end row 53 indicating only up to week 2.
    Then when generate the report in week 3, i need week number and its data up to week 3 (current week)

Well,
I’m working on it and it’s almost done.
I don’t have enough time to finish it all at once.
Thanks for waiting.
Best,
Armin

Here we go!

The “Date&Week” Metanode

The “Read” Metanode

Let’s see what’s going on in this workflow:
The Metanode reads your excel sheets (wk1 and wk2) and finds first date of wk1 and last date of wk2 (and the weeks) and calculates the number of weeks (although it’s obviously 8 (7 for wk2 sheet and 1 extra week from wk1) but I wanted everything to be calculated automatically, so if the data structure is changed you can still use this workflow).
Then we have the first “Create Data&Time Range” node which creates dates based on the starting date in your sheets and the number of the weeks (in weekly intervals) and a counter to create week numbers, starting from the starting week number. Column Expressions converts week numbers (produced by counter) above 52 to start from 1 again.
At the bottom of the workflow we read data from the excel sheets and join them (first week of wk1 + all weeks from wk2). Then unpivoting (Thanks to @Iris :wink:) “base forecast” and “GR (SU)” and joining them.
And finally adding the date and week columns and filtering based on the execution date.

Here is the workflow, dedicated with love! :sweat_smile::sweat_smile:
Merge&Transpose.knwf (223.7 KB)

Best,
Armin

PS: The week 1 for 2019 in your sheets has different values. Anyway, I only took the first week of wk1 sheet (week 51) and the other weeks are taken from wk2.

Hello @armingrudd,

many thanks for the solution!!!

may i know:

  1. there are 4 excel readers, from top to bottom, will be from the excel file, tab sheet “original File Received in wk1” --> “original File received in wk2” -->“original File Received in wk1” --> “original File received in wk2”?
  2. When i load the file in excel reader, should i check the box "table contains column names in row number (3?), should i check the box skip empty columns, hidden columns and empty rows? because when i try to load the file, in preview file content can see very few contents…see attached picture, is this correct?

Thanks.

Rain

Hi,

The 2 “File Reader” nodes at the top only read the start (in wk1) and end (in wk2) dates (year and week).
The 2 at the bottom read the data.
If the Excel file you shared has the same structure as your real data, then you should keep the settings that I have applied.

I checked the final output and it was exactly the same as you asked except:

Which I think is a mistake in the input.

Best,
Armin

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