Copy conditional existing data

[RPeeters]

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?

[Christina_H · Accepted 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.

[atcodedog05]

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 : )

[RPeeters]

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!

[atcodedog05]

Same to you @RPeeters 😀

[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).

[atcodedog05]

Happy to help : ) @RPeeters 

Cheers and have a nice day!