Split large file on 2 filters/ 2 criteria

Hi Guys,

I need your help with a flow. I have an excel file with 2 important conditions : supplier and responsible person for that particular supplier. The thing is that for a particular supplier I have 2 or 3 responsible persons.
I am using a Group Loop Start(filter on supplier name) - Create File(variable base file name “supplier name”) - Excel Writer (variable filepath)- Variable Loop End - but this is only giving me the file per supplier name with all responsible persons and I would want to have supplier X with responsible person A, same supplier X with responsible person B - if this is possible.

Looking forward for your guidance :slightly_smiling_face:

Thanks
Jules

Hello @IuliaM,

how is your data organized? If following:
Supplier | Responsible person
supplier A | person 1
supplier A | person 2
supplier B | person 3
supplier B | person 4

then simply add Responsible person in Group Loop Start condition. If not then try to obtain above format.

Br,
Ivan

Hi Ipzin,
Thanks for your reply. Yes, indeed my data is organized as per your input, but the fact is that the files which are created contains Supplier A and both responsible persons into the same file, and I need separate files.
Indeed I will have the same supplier name twice in my folder and I am not sure this will work.

Any other inputs/guidance?

Thanks
IuliaM

Hello @IuliaM,

in that case my suggestion should work. Please give it a try.

Br,
Ivan

Hi @ipazin
I have already give it a try and as I mentioned, this is creating a file for each supplier but if I have 2 responsible persons working on the same supplier account, i have only 1 file with both responsible persons. ( see below screenshots)

Thanks :slight_smile:


Hello @IuliaM,

the bottom printscreen is from input file or output file? Is there possibility you can share your workflow? That way I could check where is the issue cause I’m certain from Group Loop Stat node’s configuration that each iteration should have unique values of operator and vendor. If data is confidential please use some dummy data.

Br,
Ivan

KNIME_project_toshare.knwf (29.4 KB)

Hi @ipazin
Hopefully you can open this flow. If not, let me know how to share it correctly :slight_smile:

Thanks a lot for your help!

Jules

Hello @IuliaM,

the data itself is not included so can’t run it. Either don’t reset workflow while exporting either share Excel files here as well. One more time want to stress it out - please don’t share any sensitive data publicly!

Br,
Ivan

Good Morning @ipazin !
Please find attached as well the dummy reports.

Thanks in advance for your help!
IuliaGeneral Report Yesterday.xlsx (29.5 KB) General Report Today.xlsx (27.1 KB) PUMA extract Report.xlsx (422.5 KB)

Hello @IuliaM,

so this issue is following. In Create File Name legacy node you are using Vendor Name flow variable for file name and in Excel Reader you are using option overwrite so obviously you’ll get only one file per vendor as others will be overwritten. To avoid it use String Manipulation (Variable) node to create new flow variable from Vendor and Assigned operator and use it in Create File Name node.

Br,
Ivan

Hey @ipazin
WoW, thanks for this input! If it is not too much to ask can you please share some step by steps(screenshots) - as I am not really following your guidance (sorry, too complicated for a beginner, like me)

Thanks so very much!
IuliaM

Hello @IuliaM,

actually it’s one step (node) only as you can see from screenshot.

Now use newly created flow variable in Create File Name node. I named it OutputFileName but you can name it as you wish. Additionally you can add space or underscore between vendor and operator for better readability or do whatever manipulation you wish with flow variables only keep in mind it’s name should be unique.

Br,
Ivan

1 Like

Hey @ipazin
Amazing, simply amazing! It’s working like magic!

Thanks a millions for your help/guidance/support!

You rock :slight_smile:

2 Likes

Glad it’s working now @IuliaM :wink:
Ivan

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