Duplicate a row, splitting a large value from a column by 2?

[jackersantos]

I have a column with a large number (more than 14 digits) And everytime there is a number this large we would like to duplicate this row and subtract a fixed value from this column. Let's say the original number is 12345678942354, I want to subtract 9999999999999 from it, and create a new row where the original row would contain the result of this operation, and the second row would contain the rest. When we sum this 2 rows the result would be 12345678942354. Is it possible?

[DavidSkaife]

Hi @jackersantos 

Do you mean something like this?

Firstly add in a Record ID tool, then filter your numbers out where the length is greater than 14 (you'll need to convert to a string to do this), perform the calculations in a Formula tool, Transpose the data where the Record ID is a key column on both the true and false filter streams, then union back together, and finally sort on Record ID to put the data back into it's original order.

For the Formula tool i've created a field called 'Remainder' which is set as the fixed value, and in the original number field i subtract the 'remainder' from the value.

[ShankerV]

Hi @jackersantos 

One way of doing this.

Input was: 

Many thanks

Shanker V

[jackersantos]

thank you for taking time to answering me. 

I'm not sure this meets my need. The values for the same RecordID would all have to be less than 13 digits, and in the end if I group by recordID and Sum the column with the values, the total would have to match the value from the original dataset in which I have more than 13 digits. Not sure If I was clear enough. 

Something similar to this: 

The original amount is going to be bigger, but I would need something that would split this value so that the total amount of digits is less than 13. 

[jackersantos]

Thanks for answering me. Please see my reply below to the other user's message.