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
Balance: 123
Bank Acc: ghi
Balance: 456
Bank Acc: jkl
Balance: 456
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.)
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.
Raw Input:
Expected Output:
Thanks @sanket_2012 & @iCFO. You may refer above for more precise illustration.
Hello @Cs1011
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.
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.
