Hi Team,
I have following data set
| Lot Type | Department | Count of Aging | count of Aging | Sum of USD Equ | Sum of USD Qty |
| Bullion | cash | 01-07 days | 1 | 0 | 45764567 |
| Bullion | structured Products | 01-07 days | 22 | 188 | 5452345 |
| Cash MANG | Banking | 31-60 days | 33 | -33.55 | 5645 |
| Cash MANG | Reconciliation Cash | 01-07 days | 55 | 5345345 | 5454 |
| Custodian Cash | Corporate Actions | 01-07 days | 1 | 886786 | 888 |
| Custodian Cash | Corporate Actions | Over 180 days | 33 | 52352 | 44 |
| Nostro Cash | Banking | 01-07 days | 88 | -4563463 | -5454 |
| Nostro Cash | Banking | 08-15 days | 44 | -78574674 | -78.99 |
| Nostro Cash | Banking | 16-30 days | 99 | -90000 | 80.98 |
| Nostro Cash | Cash Checks | 01-07 days | 33 | 90.55 | 2325 |
| Nostro Cash | Cash Checks | 08-15 days | 21 | -545454 | -900.1 |
| Nostro Cash | Cash Checks | 16-30 days | 43 | 678999.5 | 5432.55 |
| Nostro Cash | Cash Forexs | 31-60 days | 78 | 88.98 | 900.55 |
| Nostro Cash | Cash Forexs | 91-180 days | 34 | 94345 | 5678.98 |
| Nostro Cash | Cash Forexs | Over 180 days | 90 | 785690 | 34789.58 |
Expected output :
As shown in the expected output Count of Aging Row should be converted as header, for each lot type subtotal needs to be created. and total of all subtotals should be shown at top and bottom as ABC bank and subtotal.
just for example only 2 dates are shown in the expected, all dates from count of aging column needs to be converted into header.
At last 3 columns should be added as Total count, Total sum of USD Equ, Total sum of USD qty
| | 01-07 Days | | | 08-15 Days | | | Total Count of Aging | Total Sum of USD Equ | Total Sum of USD qty |
| Lables | Count of Aging | Sum of USD Equ | Sum of USD qty | Count of Aging | Sum of USD Equ | Sum of USD qty | | | |
| ABC Bank | 200 | 1668946.55 | 51220125 | 65 | -79120128 | -979.09 | 265 | -77451181.45 | 51219145.91 |
| Nostro Cash(Sub total) | 121 | -4563372.45 | -3129 | 65 | -79120128 | -979.09 | 186 | -83683500.45 | -4108.09 |
| Banking | 88 | -4563463 | -5454 | 44 | -78574674 | -78.99 | 132 | -83138137 | -5532.99 |
| Cash Checks | 33 | 90.55 | 2325 | 21 | -545454 | -900.1 | 54 | -545363.45 | 1424.9 |
| Cash Forexs | | | | | | | 0 | 0 | 0 |
| Custodian Cash(Sub Total) | 1 | 886786 | 888 | | | | 1 | 886786 | 888 |
| Corporate Actions | 1 | 886786 | 888 | | | | 1 | 886786 | 888 |
| Cash MANG (Sub total) | 55 | 5345345 | 5454 | | | | 55 | 5345345 | 5454 |
| Banking | | | | | | | 0 | 0 | 0 |
| Reconciliation Cash | 55 | 5345345 | 5454 | | | | 55 | 5345345 | 5454 |
| Bullion (Sub Total) | 23 | 188 | 51216912 | | | | 23 | 188 | 51216912 |
| cash | 1 | 0 | 45764567 | | | | 1 | 0 | 45764567 |
| structered Products | 22 | 188 | 5452345 | | | | 22 | 188 | 5452345 |
| Banking | | | | | | | 0 | 0 | 0 |
| Subtotal | 200 | 1668946.55 | 51220125 | | | | 200 | 1668946.55 | 51220125 |
