Use LOOP till condition or any other better way?

[jamalbutt1232]

Hi, any help will be appreciated. I have a very different structure of excel like:

I would like some structure like this in the output:

Although I was trying to do some un-pivoting, but it isn’t a way of doing so. Unpivot, for now, will bring months against score as per the first table I want to make sure every table when un-pivoted shows the correct month. How can we produce this output? Even if using loop can anyone please show me how exactly? Thanks

Book1.xlsx (10.5 KB)

[ipazin]

Hello @jamalbutt1232,

and welcome to KNIME Community!

One way to approach this would be to use one Excel Reader for each group of data where months need to be column names. Like this for first group:

Then you use Unpivoting on each group followed by Concatenate node.

However if you files are changing on a regular basis and you would like to automate this process then this gets a bit complicated but doable. You should read all data from a file and build logic to detect each group which you process the same way as described above. For this approach you’ll probably need a loop. Tell me if you are after this one and I can make you example workflow if necessary.

Br,
Ivan

[jamalbutt1232]

Hi @ipazin

Thank you for replying. Yes, the file structure will be changing on weekly basis. And yes you are right, we need to automate it. Can you please provide me an example with the loop that will be wonderful. Thank you!

[elsamuel]

Here’s an example that should get you started:

elsamuel/Public – Extract groups from Excel file in a loop

[jamalbutt1232]

Thank you it has helped me. God bless you!

[Daniel_Weikert]

a combination of unpivoting and row filter should do the trick as well
bR

[ipazin]

Hello @jamalbutt1232,

here is what I had on mind:

1. Read all data from sheet
2. Clean it up and group it by adding GroupID to each row
3. Process data with Group Loop Start
4. In each iteration prepare grouped data for unpivoting
5. Unpivot and concatenate data in Loop End

Here is workflow on hub:

Grouped Excel Data Processing – ipazin

Contains a bit of (advanced) logic hidden inside Metanodes but should be easy to understand as there’s description under each node. If any questions feel free to ask.

Br,
Ivan