Hi Team,
I have a data set as below:
ID | Type | Jan$ | Feb$ | Mar$ | April$ | Year$ |
1 | test | 2000 | 5000 | 1000 | 900 | 8900 |
A Mapping table as below:
ID | record Name | Record% |
1 | abc | 30 |
1 | def | 30 |
1 | xyz | 40 |
The need is to use the mapping table and split the above table costs based on the percentage of the record. So the final data should be something like below after merging the two.:-
The cost gets split based on the record % of that row name.
ID | Type | Record name | Record % | Jan | Feb | Mar | Apr | Year |
1 | test | abc | 30 | 600 | 1500 | 300 | 270 | 2670 |
1 | test | def | 30 | 600 | 1500 | 300 | 270 | 2670 |
1 | test | xyz | 40 | 800 | 2000 | 400 | 360 | 3560 |
Total | 2000 | 5000 | 1000 | 900 | 8900 |
Should we use the transpose tool before the join or how ? I am little confused here
Solved! Go to Solution.
Hi @SouravKayal
You can achieve by using append tool to and multi-field formula tool
Use append tool to get values for all rows and multi-field formula tool to calculate percentage.
Hope this helps 🙂 Feel to ask if you have any questions
This works for one record now if i have a data set of more than 100000 records, there has to be a join to pick the name and percentage for that ID right ?
i tried this and I am getting more data than needed. if the mapping file has duplicates should we use a unique ?
my records are doubling so for a record with 10% i see 20
Hi @SouravKayal
Yes that right the key should be unique in at least one of the input or else it will cause one to many join and increase the numbers.