Input:
CountryCode Percentages
40 10
240 20
300 30
Goal:
Copy the rows with 240 and 300, duplicate them with a new country code.
240 should be duplicated into 250 with the same percentage.
240 should also be duplicated into 260 with the same percentage.
Output:
CountryCode Percentages
40 10
240 20
250 20
260 20
How I do it:
input --- split into three lines, one original, one formula for the 250, one formula for the 260 --- union back together --- unique tool to avoid duplicates --- output
Formula:
if [CountryID] = 240 then 250 else [CountryID] endif
OR I CAN USE:
Switch([CountryID], [CountryID],240,250)
Questions:
1. The formula part copies also existing rows, meaning after the union I have three rows for 40 10 and causing me to use a unique tool aterwards. Can I avoid this? Somehow not use else [CountryID] in the IF formula (or the default value in the Switch formula, [CountryID]).
2. What is better, switch of if?
3. Is there an easier way to do this without having to use three lines and union?
Solved! Go to Solution.
I think all you need to do is add a filter before your formulas. Filter out code 240, apply two formulas to change the county code, then union everything back together - both outputs from the filter for the original rows, and both formulas for the duplicates. Doing it this way there's also no need for if or switch formulas, just change the value to 250/260.
Hi @RPeeters
Here is another way of doing it. You can adjust the range in gen rows tool and easily extend the range.
Workflow:
Hope this helps : )
Thanks @atcodedog05 / @Christina_H , I knew there would be better solutions and I am glad to see there is still things to learn. Coming monday I will test both of your suggestions. Have a good weekend!
Same to you @RPeeters 😀
@atcodedog05 you idea is nice and thanks for showing it, however I think the filter with and IF formula works better in my case as in reality the first values (CountryCode) are more variable, I just kept it similar for clarity in this post (240 and afterwards increments of 10, while it is more random in my real data).
Happy to help : ) @RPeeters
Cheers and have a nice day!