concatenate 2 Tabels with different headline

[JHoc]

Hello all,

situation is as following:
i have several files which contain measurement data from a testbench. Each measurement is in a single folder which contains the different steps of the testrun.
Data is saved in a json file.
For example: File 1.json contains some information about the specimen (ID, Name, company)
File 2.json contains information and values from sensors (2000 double values)
File 3.json contains valuse from the next step and so on.

What i want to do is to have all this information of the testrun in one table. Unfortunately, the ID from the part is only available in File 1.

What i have done so far is:

• i read File 1.json from all the folders with a ListFile/folder node and stored it in one table (i used loop end(column append)).
• I did the same with File 2 and 3

Now i try to concatenate these three different tabels to have all the data in one table. (the ID is the most important)
But i have no idea how to get this ID in the table with the sensor values (colum headers do not match). I tried renaming the column name but honestly i do not want to do this for every single measurement (there are about 5000 at the moment).
Do you maybe have a better idea how to solve this?

Thanks a lot for your suggestions and have a nice weekend.

[denisfi]

Hi @JHoc ,

For better example, can you bring a dummy workflow about it? It will help us to understand and have some data to see and make some suggestions.

Until now, I understand that you can use 3 json readers node to load all the data information separatelly. From the file 1 will create a full table with the Json Path node.

For the others files, maybe you can join it if you have something to classified them. After that, you can make another join to match both examples and export only the data that you need (match, left data or right data) or just put all together.

I’m blind here, so I’ll wait your wf example ok?

BR,

Denis

[JHoc]

I try to create a dummy wf. This may take a while.
You are right.
Right now, i use 3 Listfile/folder nodes, 3 Json readers (for the 3 different files), and 2 RowID nodes.
Despite of File 1, Knime uses the timestamp from the sensor values as row id. but these wont match with a different measurement. So i used this for having the values in Row IDs Row0 Row1, etc.
Unfortunately, there is nothing in the files to join them.
I also thought about it to write the ID in the original files by using powershell or something similar.
But this is only a workaround i will have to do this for every further measuerement i will receive.

[JHoc]

This was much faster than expected.
I exported a dummy WF with some dummydata included. In the Concatenate node, you will see the problem immediately. I want to have the sensor values in the same column as the ID.
testdummy.knwf (59.1 KB)

[denisfi]

Well,

A tip here: when you use the Row ID node, you can bring a reset the ID and you can add the data as a column information too, sou you can kill 2 process at once.

I’ll call the data as “ID” to you see it and reset the rowid count.

If you return one step from the transpose for the first line, you’ll see the right organization to make a join process and compare it.

You have date info as column to it.

And from the second line, i’ll bring it with rowid as I told before:

I can’t change the workflow because I don’t have the files to test again, but you can try and tell us later, ok?

BR,

Denis

[JHoc]

Wow. Thanks a lot for you effort. I tried to add the dummydata as well, but i can not upload a .zip file.
I think the date column can not be used, because this timestamp also contains ms and is almost unique.
Maybe i got you wrong…

[denisfi]

you can solve the date problem with some string manipulation expressions or date&time nodes. If you looking just for a date info, you can convert it, removing the time and make the join.

BR,

Denis