Formula to split data

[SouravKayal]

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

[atcodedog05]

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

[SouravKayal]

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 ?

[atcodedog05 · Accepted solution]

Hi @SouravKayal 

Yes thats right. It would be something like this and join on ID

[SouravKayal]

i tried this and I am getting more data than needed. if the mapping file has duplicates should we use a unique ?

[SouravKayal]

my records are doubling so for a record with 10% i see 20

[atcodedog05]

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.