I have data like below and i need to create a pivot.
| ID | Month | Added_falg | Removed_flag | Removed_Month | type |
| 1 | 1 | A | null | null | 1 |
| 2 | 1 | A | null | null | 1 |
| 3 | 1 | null | R | 2 | 1 |
| 4 | 2 | A | null | nul | 1 |
| 5 | 2 | A | null | null | 1 |
| 1 | 2 | NC | R | 3 | 1 |
| 2 | 2 | NC | R | 3 | 1 |
| 4 | 3 | NC | null | nul | 1 |
| 5 | 3 | NC | null | null | 1 |
I have to get result as
| Type | M1 Total | M1 Added (added_flag as A) | M1 Removed | M2 Total | M2 Added | M2 Removed | M3 net | M3 added | M3 Removed |
| 1 | 3 | 2 | 0 | 4 | 2 | 1 | 2 | 0 | 2 |
Previous month count of 'R' (removed_flag) to be tagged to next month. Ex. M1 -R records to be counted as part of M2 Removed (We have a column removed month which represent M2)
But i can able to plot as ..new to qlik and any help should be appreciated.
in SQl, i have union 'R' records with removed_month as month and can able to get the output.
| Type | M1 net | M1 Added (added_flag as A) | M1 Removed | M2 net | M2 Added | M2 Removed | M3 net | M3 added | M3 Removed |
| 1 | 3 | 2 | 1 | 4 | 2 | 2 | 2 | 0 | 0 |
