I have to compare 2 headers from 2 excelsheets, both headers located in row 1 of the sheets.
I have in mind to save header from sheet1 in variable1 and header from sheet2 in variable2 and then doing a compare.
From sheet1: if the cell contents <> “abc” then add cell contents into variable1 separated by “##” using a loop to read 20 cells
From sheet2: if the cell contents <> “abc” then add cell contents into variable2 separated by “##” using a loop to read 20 cells
Compare variable1 against variable2 with a node, if equal output port is green, else it is red.
Does anyone has an idea, maybe a better idea than I had?
Welcome to the KNIME community! I think there will be various ways to solve your problem, so i think in the end it might come down to personal preference and what you want to do with the information afterwards.
One suggestion would be to read the two excel sheets with two separate -Excel Reader- nodes, then use the -Extract Column Header- node on each of them. You could set this to extract column headers as a single column. Then you could use the -Joiner- node to do a full outer join and split between separate output ports.
Excel 1 has 54 headercells
Excel2 has same 54 headercells
Join result: rows 0, cols 104
Left unmatched: rows 1, cols 52
Right unmatched: rows 0, cols 52
I am expecting Left unmatched: rows 0 (Excel1 is the master; I need output to see if headercells from excel1 can be found in excel2 in the same folk order)
Joiner settings Top input left containing all header cells from excel1, top input right containing all header cells from excel2.
Compare values in join = compare by values in join columns = value and type
Include in output = left unmatched rows
Output options = split join result…
If I understand correctly, you would like to make sure that the columns in Excel File 2 are in the same order as Excel File 1.
In fact, the node -Reference Column Resorter- can reorder the columns in a table based on a reference list of columns. So perhaps this is what you need rather than just checking if they are in the correct order?
If so, you can keep the -Extract Column Header- node for Excel 1 and make sure it is set to extract the column header as a column:
Then use the -Reference Column Resorter- node with Excel File 2 going into the top port and the extracted list of column headers going into the bottom port. You can decide how you want to deal with any extra columns, but it sounds like in your case there will be none:
I’ve added this to the workflow I previously created:
I’ve additionally added a solution using the -Column Appender- and -Rule-Engine- nodes, assigning TRUE or FALSE depending on whether the columns match.
Hopefully one of these 2 solutions will help,
Thanks
Heather
Hi Heather,
thanks a lot; the rule engine gives me the result I want.
Is there a node existing showing me e.g. a green light when all columns are matching or a red light when one or more columns are not matching like a traffic light?
I would like to use this as an indicator showing everything ok or not.
I think you could achieve a similar behaviour by using the Case Switch or If Switch.
The switches send data to one output port or another depending on a prior set of rules (using a Rule Engine and Table Row to Variable node).
You can create a rule so that if the columns match the switch opens the top port and closes the bottom port. Then if the columns don’t match, the switch opens the bottom port and closes the top port.
I’m replying from my mobile, so I can’t offer a workflow right now but I will try to post one later if you need.
Edit: I have just seen there was another reply, so you could try all the suggestions and see which works for you
