Hi,
I have a requirement where I need to split 1 code from raw file to individual codes from a reference file. For example code A will be split into Code X, Code Y and Code Z. Their costs are also divided into individual code costs depending on percentages in file. I was able to split the code into different codes with transpose functionality to a certain extent. I am attaching my input and output data screenshot below.
| Input |
| code | name | type | item | calc cost | calc Quant |
| A | Category A | Category A | X123 | NULL | NULL |
| A | Category B | Category B | X124 | NULL | NULL |
| A | Category C | Category C | [Null] | NULL | NULL |
| A | Category D | Category D | [Null] | NULL | NULL |
| A | Category A cost | Category A | NULL | 10 | NULL |
| A | Category B cost | Category B | NULL | 90 | NULL |
| A | Category C cost | Category C | NULL | 0 | NULL |
| A | Category D cost | Category D | NULL | 0 | NULL |
| A | Category A # | Category A | NULL | NULL | 3 |
| A | Category B # | Category B | NULL | NULL | 7 |
| A | Category C # | Category C | NULL | NULL | 0 |
| A | Category D # | Category D | NULL | NULL | 0 |
| B | Category A | Category A | X125 | NULL | NULL |
| B | Category B | Category B | X123 | NULL | NULL |
| B | Category C | Category C | X126 | NULL | NULL |
| B | Category D | Category D | [Null] | NULL | NULL |
| B | Category A cost | Category A | NULL | 40 | NULL |
| B | Category B cost | Category B | NULL | 80 | NULL |
| B | Category C cost | Category C | NULL | 80 | NULL |
| B | Category D cost | Category D | NULL | 0 | NULL |
| B | Category A # | Category A | NULL | NULL | 8 |
| B | Category B # | Category B | NULL | NULL | 6 |
| B | Category C # | Category C | NULL | NULL | 6 |
| B | Category D # | Category D | NULL | NULL | 0 |
Output:
| Output |
| code | type | item | calc cost | calc Quant |
| A | Category A | X123 | 10 | 3 |
| A | Category B | X124 | 90 | 7 |
| A | Category C | NULL | 0 | 0 |
| A | Category D | NULL | 0 | 0 |
| B | Category A | X125 | 40 | 8 |
| B | Category B | X123 | 80 | 6 |
| B | Category C | X126 | 80 | 6 |
| B | Category D | NULL | 0 | 0 |
