grep information

[tommasopalomba]

Hi All,

I have a list of objects (txt file1) and a sdf file containing several information. For each object in file1 I need to extract related informations from the sdf. The problem is that in the sdf file I can have more than one row associated to the same object.
An example below:

file1.txt

AAA
BBB
CCC

file.sdf

column1,column2,column3
AAA,1,1
BBB,2,2
AAA,0,0
CCC,3,3
CCC,1,1

Expected output:
AAA,1,1,0,0
BBB,2,2
CCC,3,3,1,1

How can I do that? I tried with JOIN node but I didn’t get what I would.

Thanks in advance.

[HeatherPikairos]

Hi @tommasopalomba

You could try using the -GroupBy- node on just “file.sdf”. Choose column1 as the Group column, and use column2 and column3 in the manual aggregation tab. Choose the aggregation method “List” for both.
You could then use the -Split Collection Column- node to change the lists into separate columns.

This would however create slightly different ordering of columns in the end result:

AAA,1,0, 1,0
BBB,2,missing value,2,missing value
CCC,3,1,3,1

You could then reorder them if required

Hope that helps
Heather