Merge data from few .xls sheets (Excel) into one file, depending on conditions, and then create a xml file from all gathered data

Hi, I am new to Knime. Now working with Excel files, I want to merge data from few sheets, depending on conditions, for example: I have one sheet with some values, and depending on whether value appear in column G, then I want to merge related data from other sheet (not necessary in one sheet, just gather these data, and then create from all gathered data a xml file). Help will be much appreciated.

Hey @bienio,

please explain your approach in a little bit more detail, attach some example data and describe the output you are aiming for.

Kind regards,

Patrick

Hi, thank you for your respond. I am sending example file data.

2 sheets in xls file (data1), merging data if in Sheet1 column E have value Y (then adding data from Sheet2 to this certain id (eg. test2)).

outpout.xml is result I want to achieve (data from Sheet2 merged with data from Shee1 (test2, E)).

data1.xlsx (8.6 KB)
output.xml (329 Bytes)

Hey @bienio,

two possible approaches:
(1): Perform a full outer join on the data and filter afterwards on ColumnE=“Y”

(2): Filter data1 on ColumnE=“Y” and perform a full outer join

Regarding building the XML file, I can’t help you - sorry. That’s not my area of expertise.

Kind regards,

Patrick

1 Like

Thank you very much for your help. I forgot to mention: I would like to display also these data that don’t have a match, with 0 values (I’ve updated output file). Can you also help me?

output.xml (530 Bytes)

Hey @bienio,

there is the “Missing Value”-node. Using this node, you can replace missing values by fix values (e.g., zeroes or spaces). Just add the node after the joiner and remove the filter node.

1 Like

Thank you Patrick for your help, but still I have a problem with getting this certain result in xml, like in output I uploaded.

Coulmn E in xml as a markup, without value from Sheet1 (I’ve changed it), but with values in markups from Sheet2 (if are missing, then 0’s). Anybody?

output.xml (526 Bytes)
data1.xlsx (8.6 KB)

Hey @bienio,

I’m not the XML guy myself - so my advise is to have a look at the XML nodes and read the inline help files. Maybe there are some other forum threads about that - please check this.

Good news is, you managed to get the data you were looking form - last challenge is ‘just’ to get them into the right form.

Fingers crossed and kind regards,

Patrick

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