I know the group by node and to group the unique count mixtures and unique count materials. But I am not able to group the amount in a correct way. Important here is, that substance 1 is available in material a with 5 l, material b with 2 l and material c with 2g. So in total 7 l and 2 g.
That´s the first hurdle which I want to solve.
If this can be solved how can I even group by different units together, e.g. 5 l, 2 l and 100 ml are 7.1 l? In my dataset I currently have 7 different units (might get more).
put the Substance and Unit column to the group condition. Sum your Amount column and Concatenate your Material column to keep the information of the Source.
Hi @Alkaline,
i think you need a 2 step approach. First you should summarize your data based on their units. Therefore you should include your unit into the group conditions of your group by node.
I also thought about this before. This works with a smaller list. But I have some substances beeing available in 10 000 of materials. And I don´t want to have a concatenation of all 10 000 individual amounts but at least group everything with the same unit (e.g. substance 5 is available in 5000 mixtures and 10 000 materials with in total 1378905 l and 394939 g).
Hi @Alkaline
taking unit in group condition is necessary get one record for each substance and unit and afterward you must combine your total amounts and units to a string and use a second group by node grouped only by substance to concatenate these strings.
If you want to have an overall unique count for mixtures and material you need a third group by node which you have finally join with your previous result.
you can try following approach. Use String Manipulation node and join() function to join amount and unit columns into new column. Then simply use GroupBy node where Substance is your grouping column,on Mixture and Material columns apply Unique count method and on newly created column apply Concatenate method.
To go from 5 l, 2 l and 100 ml to 7.1 l you would need to do some unit conversions. But as not all units per substance can be summed this seems a bit complex and an input data with desired output would help a lot in order to get more suggestions
