Question to get an output

[JonamDeju]

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

TABLE A:

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:

TABLE B

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?

[ScottF]

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

[JonamDeju]

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!

Thanks,
Jonam

[ScottF]

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…

[JonamDeju]

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!

[ScottF]

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

[JonamDeju]

Thank you so much for looking into this!

[ScottF]

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.

[mlauber71]

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

example for Pivot table – mlauber71

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

[Thyme]

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