Pivoting node

[Ulzhan]

Good day Knime community!

I would be really thankful if anybody could me with the issue I faced with pivoting node. The issue is I have some row data with some 0 values and when I am executing the pivoting node, those 0 values will not show up in the output table. Could anybody suggest troubleshooting steps to solve this issue?

Please also let me know if you need any more details,

Many thanks,
Ulzhan

[HansS]

hi @Ulzhan

Do you mean “0” of “NULL” or “?”. In case of “?” a missing value use the Missing Value node. Otherwise use a Rule Engine to give your 0-values some value.
gr. Hans

[Ulzhan]

Hi @HansS,

It is missing values “?” and I want them to show up as 0 in the output table.

I used Missing Values node as you suggested and set that to String - Value - 0. But it still gives me a message “Column still contains missing values.”.

[ipazin]

Hello @Ulzhan,

handling of missing values in Pivoting node depends on aggregation method you are using and it’s settings. But it would be best to share workflow example. If your data is confidential use some dummy data to show us your problem.

Br,
Ivan

[HansS]

Did you the second tab in the Missing Value Node?

[Ulzhan]

Hey @HansS,

I think Missing Value node could actually help me here. But I am actually changing my question because I think I didn’t explain it properly. The thing is I have a report template that should be populated with input data, and there could be cases that some record type is not present in the input data and that should be reported as 0 in the template. I know that Missing Value node will not create me those records, but it indeed helped me with rest of the data where I had missing values.

Do you think that there is a way to handle this issue?

Thanks,
Ulzhan

[ipazin]

Hello @Ulzhan,

maybe this topics helps:

Add Row based on filters

Hello everybody, I am looking for a solution as follows. I am extracting data into a table and group it by Code and period. That is fine and it works. But sometimes I have for some codes not the same number of rows. That is something I need. How can I loop through the table and add a row, to but values into the specific fields. Here a short example. row ID Code period OK NOK HIGH RISK STATUS Row0 123 2019/01 135 26 0 3 Row1 123 2019/02 137 24 0 3 Row2 123 2019/03 135 26 0 3 Row3 …

Br,
Ivan

[morpheus]

Hi @Ulzhan,

one option is to create a dummy table by
a) using groupby node to generate unique table entries for all your row condition of your pivot table.
b) using groupby node to generate unique table entries for all your column condition of your pivot table.
c) use cross joiner on both goup by results to get all possible rows.
d) join your initial table with left outer join setting to your cross joiner result.
e) apply missing value node
d) pivot your results.

BR
Hermann