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
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.
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.