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?
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!
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