Negating columns for some rows in a dataset

Question
1

I am merging data from two finance systems, where I have different sign of numbers for some of the accounts in the chart of accounts (income accounts appear with negative values in one, and positive in the other). Since I want to merge the datasets I need to negate one set of these numbers, but only for some rows.

What is the simplest way of doing this using EDT? My thinking so far is to split based on account number (eg accounts < 4000) into a separate dataset using filter, multiply cells with -1, and then merge back. Better ideas? I need to do this for 8 periods (8 set of columns).

2

It would probably be a lot easier to use an If transform to create a new column with a value of -1 or +1. Then use Calculate to multiply by that new column to get the corrected value.

image
image620×131 33 KB

image
image581×603 104 KB

balance-correction.transform (4.1 KB)

2 Likes
3

@egilDOTnet if it is just 8 balance columns you can do the calculate 8 times. If it would be more, Gather and Spread might be an option.

image
image2362×198 64.8 KB

I simulated here with 8 columns for balances.

balance-correction 2.transform (6.9 KB)

2 Likes
4

Brilliant, thanks both of you!!

2 Likes