Question to get an output

Left table (Table A) consists of PnL data, for each transaction month, book name, measure name, Book eligible for regulatory review


Transaction ID Month Book Name Measure Name Eligible for regulatory review
TXN_101 2020/01/01 A01H.HS Bond FX Y
TXN_102 2020/01/01 TUV.UI FX Reset Y
TXN_103 2020/02/02 A92H.HK Bond Adj. N
TXN_104 2020/02/19 A92H.HK Carry FX Y
TXN_105 2020/02/26 A92H.HK FX Cash Y
TXN_106 2020/02/28 TUV.UI Bond Adj. N
TXN_107 2020/04/07 A01H.HS Carry FX Y
TXN_108 2020/04/08 A92H.HK FX Cash Y
TXN_109 2020/04/08 TUV.UI Bond Adj. Y

Output expected:


Month A01H.HS A92H.HK TUV.UI
JAN 1 2 1
FEB 0 2 0
APR 1 1 1

What is the configuration for output shown as TABLE B?

I have tried pivoting, unpivoting and group-by nodes still no luck. And i just started learning loop concepts in knime so can someone help me with this please?

Hi @JonamDeju -

First you need to convert the month to Date&Time Format and extract the month.

Then you can use the Pivoting node to group on the newly extracted month, pivot on book name, with aggregation of count.

Give it a try and if you’re still stuck someone can create an example workflow for you :slight_smile:


Hi @ScottF , Thanks much for your response!

I have tried this and this is how the output comes for me and not like the output expected.


Any help would be highly appreciated!


Looking closely at your the Table B from your initial post, I think it’s incorrect. For example it shows a total of 5 for A92H.HK, but in Table A that book name only appears 4 times.

As best I can tell, the second Table B you posted is correct, right? Or else there’s some other aspect of the problem that you’ve not yet mentioned…


Yes, I believe The Table B I posted here is correct and if that’s the case as you say, please check the value TUV.UI and it appears 1 time in FEB month but in the output it shows as 0.

So there is an another logic works here and I am having a hard time to get this! :frowning:

Maybe there’s an issue with locale in the Date&Time conversion? Let me try to mock something up right quick.


Thank you so much for looking into this!

I think your initial Table B has to be wrong. Check each of the cells manually.

When I do it in KNIME, I got the same answer as your second Table B, which matches with Table A.

1 Like

@JonamDeju this could be one way to do it, but as @ScottF has said the result is different from your table. So you might want to check the input.

If you would take into account your “Eligible for regulatory review” column and use Y as 1 and N as 0 it would look like this (and sort the column names):


There’s definitely something wrong with the example tables. I see no way of matching both. The easy way out is to discard table B as faulty. However, I might have found a pattern:

Filtering out rows that are not eligible for regulatory view, Feb and April match with example table B. Possible explanation: Table A is missing some rows for January and only ‘Y’ rows should be considered. @JonamDeju can you confirm? Do you have more info on your problem other than the input and the desired output?

PS: I didn’t see mlauber mentioning the eligibility column until checking everything again before posting. I promise I came up with that on my own :sweat_smile:


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