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 |
Hey @Jasdev,
Is this the sort of solution your looking for:
Any questions or issues please ask :)
HTH!
Ira
hey @Jasdev
Let me know if this works out for you. Transpose and take out the nulls and cross tab again.