Adding Missing data

Hi Knime community,

I have this dummy data set:

Screen Shot 2020-12-03 at 22.32.37

and what I want to do is make a pivot where I would have code as rows and residency as columns with aggregation function sum, and I also would like to include code A1 which is missing from my data:

Screen Shot 2020-12-03 at 22.37.31

I would really appreciate if anyone could me help deal with this missing data issues.

Thanks.

Use the Pivot node to create your pivot table: group on Code, pivot on Resident, aggregate on Balance using sum.

For adding the row with A1, use the Table Creator node to create the row, and then use the Concatenate node to add it to the pivoted table.

2 Likes

Hi @Ulzhan
i assume that in a normal case there is a reference or master table with the unique codes available. If so and if you have access to the list you could use the joiner node to solve the missing Code issue.

BR
Hermann

1 Like

Hi Hermann,

Thanks for your response.

If I do have a reference/master table, could you elaborate more on how to use joiner node?

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.