REPLACING VALUES IN A COLUMN INTO A NEW DATA

[dunkindonut7777]

Hi, I would like to know if how to replace the values under account type column particularly the OTHER INCOME (EXPENSES) text. I want to replace the test "OTHER INCOME (EXPENSES)" into REVENUE.

From this:

G/L acct	ACCOUNT TYPE	ACCOUNT SUB TYPE
1	OTHER INCOME (EXPENSES)	OTHER INCOME (EXPENSES)
4	OTHER INCOME (EXPENSES)	OTHER INCOME (EXPENSES)
5	EQUITY	TOTAL EQUITY
6	EQUITY	TOTAL EQUITY

to this:

G/L acct	ACCOUNT TYPE	ACCOUNT SUB TYPE
1	REVENUE	OTHER INCOME (EXPENSES)
4	REVENUE	OTHER INCOME (EXPENSES)
5	EQUITY	TOTAL EQUITY
6	EQUITY	TOTAL EQUITY

Can you help me with this one please?

[vizAlter]

Hi @dunkindonut7777 — You can update the values by two tools/tricks, see below along with attached workflow:

1) Use a "Formula" tool if you want to replace just one or two or very limited text (like you have asked for just one value):

2) Use a "Find Replace" tool if you want to pass a list of values to replace:

(Note - This tool works only with String data types)

[Emil_Kos]

Hi @dunkindonut7777,

I am not an expert but for my best knowledge, the first solution proposed by @vizAlter should work faster.

I would suggest using the second option if you would have many positions that you want to replace. Thanks to that it would be more convenient for you to maintain the workflow. 

[dunkindonut7777]

Thank you for your help! 🙂 It works but there's another issue I want to ask,  how about this one:

I want to change the "Revenue" into "Expense"

From this:

ACCOUNT TYPE	ACCOUNT SUB TYPE	ACCOUNT CLASS
REVENUE	OTHER INCOME (EXPENSES)	OTHER INCOME
REVENUE	OTHER INCOME (EXPENSES)	INTEREST EXPENSE

to this:

ACCOUNT TYPE	ACCOUNT SUB TYPE	ACCOUNT CLASS
REVENUE	OTHER INCOME (EXPENSES)	OTHER INCOME
EXPENSE	OTHER INCOME (EXPENSES)	INTEREST EXPENSE

[sazit]

Hi clodelegonzales,

Similar to the solution provided above, you can use a formula tool to change your value of the field based on any condition.

For your case it seems to be the case that if ACCOUNT CLASS is "INTEREST EXPENSE" then ACCOUNT TYPE should be EXPENSE else, you should leave the value as is.

So you can create a new formula tool to update the field ACCOUNT TYPE with the following statement:

IF [ACCOUNT CLASS] = "INTEREST EXPENSE"

then "EXPENSE"

else [ACCOUNT CLASS]

ENDIF