Loop on the basis of single Excel sheet data

[sahil786]

Hi All,

I am having an excel sheet which contain multiple form data in it.I want to separate the data for each form on the basis of formname and by making use of loop ,I want to run it one by one.Attaching the data.
Each form data can hv any number of columns with it.

[mlauber71]

@sahil786 I think you can use the

Group Loop Start

---

Which loop is the right one for me?
https://blog.statinfer.com/which-loop-is-the-right-one-for-me/

Workflow control - Loops
https://www.knime.com/self-paced-course/l2-ds-knime-analytics-platform-for-data-scientists-advanced/lesson3

Node Guide to loops
https://www.knime.com/nodeguide/control-structures/loops

[sahil786]

Thank u for the response .But I dont have fix group. Dis the only problem.
I am thinking to generate a numerical column in order to identify a group on the basis of col0(whenever col0==FORMNAME) that new column value should be incremented .
For eg:please see image

[mlauber71]

OK I understand. You have something like a header which is FORNAME. I think you could use a combination of Rule based row filter, LAG column and Missing value node to fill the group column. Could you just upload your sample file.

[sahil786]

Book1.xlsx (9.8 KB)

[sahil786]

Also I am using the below logic in column expression…But it is not working.It is not holding the current value of variable a

[mlauber71]

OK it was slightly more complicated than I initially thought. But I hope it does work now:

• give each line a unique ID to identify them later
• identify the ‘heads’ of the tables (“FORMNAME”) like you already did
• use moving aggregate to give the heads unique numbers
• bring these numbers back to the data
• fill the intermediate (empty) cells with the head’s number
• use a group loop on the blocks identified by the head numbers
• extract the first line as column names
• make sure empty columns that would just contain missing values are excluded (you could set a threshold)
• insert the column names
• create a new file name
• store the new files on disk
• be done

kn_forum_26510_excel_single_file_loop – mlauber71

[ipazin]

Hi @sahil786,

see this workflow example how to use global variables in Column Expressions node:
Usage of Global Variables in Column Expressions nodes

Br,
Ivan

[sahil786]

Hi mlauber/ipazin,
Thank u so much for the workflow​:grinning:.it worked for me…