Guys, I’m new to KNIME and I need some help, please.
I have a tax file in TXT format and I need to make changes to part of it and save it in the same file format. The need for change is only in the lines below this CNPJ block. The change cannot be made if the block starts with another CNPJ.
A sample of the data follows. The blocks are huge.
|C001|0|
|C010|15436940000103|1| <— beginning of block
| C180|55|09012019|3101 2019|B000FA5KX2 |49019900||334|76|
|C181|06|5949|83|69||0|0|||0|40100|
|C181|06|6108|251|07||0|0|||0|40100|
|C185|06|5949|83|69||0|0|||0|40100|
|C185|06|6108|251|07||0|0|||0|40100|
|C180|55|22012019|22012019|B000FA5QHM|49019900||26|46|
|C010|15436940000367|1| <— start of another block
| C180|55|23:01 2019|3101 2019|8551003046|49019900||2508|7|
|C181|06|5102|857|76||0|0|||0|40100|
|C181|06|6108|1650|94||0|0|||0|40100|
|C185|06|5102|857|76||0|0|||0|40100|
At first, I used the “Colum Expressions” node. Below is the attempt code:
if (column (“Col0"), “|C181|06|”, or (“|C185|06|6108|”), and (“5102", “5405”, “6108”, “5949”) == true)
@andymesmo , welcome to KNIME forum. If you know the length of the first block, using RowID or RowIndex function you can add row number to the existing logic.
In fact during my attempts I used the RowSpliter (Labs) node using the start rowid and the end rowid that I saw through Notepad++ (But each file has a position. I manage to open it in Notepad++ I use the same criteria and apply it to the others). The goal was to separate these rowid. I used node colum expressions to apply the change rule to this block that I separated by rowid after reading the Row Splitter (Labs) node. Now I am unable to merge this changed information with the original information so that I merge the changed information by replacing the original. I appreciate if you can help me by indicating how to do it in KNIME thank
Sequence of my data flow:
File Reader > Row Splitter (Lab) > Column Expressions > Colum Filter
So far I managed to bring only the changed block. Now I want to merge replacing the same rowid from the original records with the changed ones.
Yes, the Concatenate Node is the one you want. Unfortunately it will append the incoming tables to the end. If you want to keep the row order from before the Row Splitter, you need to do something like this:
Append a column with the rowindex (RowIDs are strings, those can’t be used for sorting)
Split the table
Use your Column Expression on one of the branches
Glue the branches together with the Concatenate node
I’m insisting on this node. It’s not joining the columns even though I select the append suffix. I must be doing something wrong. If you can, access the link below where I left the dataflow and the file I’m using to help me. Thank you very much once again!
other nodes I tried:
column aggregator
column merge
The Row Splitter (Labs) node is exactly on port 0 the data to change and on port 1 only the data that does not need to be changed.
It looks like you are appending a new column with the Column Expressions Node. Your modified column then has a different name, which will result in a lot of missing value in the concatenated table. If you scroll down to the bottom, “Col0” should be missing values and “alterado” should hold the modified values. (missing values are red question marks)
The easiest fix is to select “replace column” in the column expression. This makes the Column Filter obsolete as well.
Thank you very much for your didactics. I’m using your method however, when checking if there was a change at the end of the node before leaving, I see that it didn’t happen. If we go back and see the node after the expression, we see that the node concatenate check changes what is needed. By joining with the original column that is not happening. First of all, I apologize for the doubtful work I am bringing. I’m the one who might be using it incorrectly. Can you correct me if necessary to use the nodes.
