Pivoting Data which Pivot category is inconsistent

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

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,
Sanket

2 Likes

Hi @sanket_2012,

Thanks for suggestion! I have tried but unfortunately it turned out differently - please refer below.

Expected Outcome
image

Outcome (using table transposer as per suggested)

Any clue to solve?

Thanks!

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?

Thanks,
Sanket

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.)

2 Likes

Hello @Cs1011
There are multiple options for this challenge. Please find attached this proposed workflow:

20231103_pivoting_data_v1.knwf (33.7 KB)

BR

3 Likes

@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.

Raw Input:
image

Expected Output:
image

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.

BR

1 Like

Hi @gonhaddock,

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.

Raw Input:
image

Output:
image

Expected Output:
image

20231103_pivoting_data_v2.knwf (64.3 KB)

hi Cs1011
my loop approach,
image
20231103_acc_MM.knwf (130.4 KB)

rgds
linux knime 5.1.x

Hi @marzukim,

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.

Thanks

@Cs1011

Please find updated workflow, this different approach should be valid for your current use case:

20231103_pivoting_data_v4.knwf (66.0 KB)

BR

1 Like

hi @Cs1011

My suggestion would be to create a Bank Acc / Balance group and pivot value/account subject to that. see example

image

image image

cheers

1 Like

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