I would like to transpose bank statements and I tried to solve by pivoting (manual aggregation with list) and then ungrouping them.
It does not meet exactly the expected outcome as the pivot category is inconsistent. Refer to example below, certain sets come with “bank acc” and " balance" while some do not have “balance”. The balance of second set will be transposed into the same row as first bank acc as balance for first bank acc is not available.
Any idea we could have the outcome which first bank acc is with “0” under balance column?
Bank Acc: abc
Bank Acc: def
Bank Acc: ghi
Bank Acc: jkl
Thanks in advance
Hello @Cs1011 ,
Welcome to the KNIME Community!
Have you tried using the Table Transposer node and then fix the missing value using the Missing value node?
Thanks for suggestion! I have tried but unfortunately it turned out differently - please refer below.
Outcome (using table transposer as per suggested)
Any clue to solve?
Hi @Cs1011 ,
I understood the expected outcome, thanks.
What is the exact structure of your input in KNIME? Can you send a snapshot of it?
I can’t really tell if your first post is a desired output or the input table…
It seems to me like you either just need to replace the missing values with zero, or need to insert a row when missing that contains a zero value. If the missing value row is present, then use the Missing Values node. If you need to insert a row with a zero value when it is missing then you would likely need to group loop / conditional insert. (Or perhaps number the rows, join in the necessary added rows with correct number logic for placement, and then sort to get correct order.)
There are multiple options for this challenge. Please find attached this proposed workflow:
20231103_pivoting_data_v1.knwf (33.7 KB)
@gonhaddock Thanks! that is very close.
I understand that you leverage on “string to number” to distinguish balances (number) from bank acc (string).
What if both balances and bank acc are in numbers in the scenarios mentioned in the first post? And how can I eliminate those account numbers (green dotted lines) that do not have balances that comes immediately in next line?
Please refer below for more precise illustration.
Thanks @sanket_2012 & @iCFO. You may refer above for more precise illustration.
You can replace the ‘String to Number’ node with a ‘Rule Engine’ with the following code, and replacing $Balance$ column:
$Category$ LIKE "Balance" => $Balance$
At some point you will have to convert it to double I guess
If you just replace the node:
The Column Expressions is configured to return double
The Moving Aggregation would need to be reconfigured as the column format type changed.
Optionally you can keep the String to Number after the Rule Engine, and everything will work smooth.
Appreciate the advice!
I have tried as per advice, but it does not meet the expected outcome still, much appreciate if you could advise further. Kindly please refer to attached knwf attachment.
20231103_pivoting_data_v2.knwf (64.3 KB)
my loop approach,
20231103_acc_MM.knwf (130.4 KB)
linux knime 5.1.x
You screenshot outcome seems aligned with my expected outcome.
However, Not sure why I am not able to load your workflow in my KNIME on my windows laptop, any idea?
My KNIME version is at 4.7, not sure why it is not available to update to version 5.1.
Please find updated workflow, this different approach should be valid for your current use case:
20231103_pivoting_data_v4.knwf (66.0 KB)
My suggestion would be to create a Bank Acc / Balance group and pivot value/account subject to that. see example