Ensure all Entities have the same GL Accounts and working around GL accounts with Letters

[davidlocke]

I have a 2 part question.  First is when we pull the trial balance from our ERP system it pulls only accounts with balances, but for various calculations that will be done outside of alteryx, I need each entity to have the same GL accounts.  Below is a quick example.

TBs from ERP:

Entity	A/C	Desc	Amt
1000	610111	Small F&F <$1,000	100
1000	610130	Badging Expenses	100
1000	610200	Sm Equip $1,000-$5,0	100
1000	610201	Small Equip <$1,000	100
1000	610250	Security Equipment	100
1000	610300	Print/Repro/Copy Cst	100
1000	610400	Forms	100
1000	610500	Safety Supplies	100
1000	610600	Research Material	100
1000	610700	Samples	100
1000	T00101	162m Tax Account - Salaries & Wages	100
1000	T00102	162m Tax Account - Stock Option Expense (NQ)	100
1000	T00103	162m Tax Account - Other Equity Based Comp (RSU, PSU)	100
1000	T00104	GILTI Inclusion	100
1000	T00501	162m Tax Account - COGS Salaries & Wages	100
1000	T00502	162m Tax Account - COGS Stock Option Expense (NQ)	100
1000	T00503	162m Tax Account - COGS Other Equity Based Comp (RSU, PSU)	100
1000	T00701	162m Tax Account - R&D Salaries & Wages	100
1000	T00702	162m Tax Account - R&D Stock Option Expense (NQ)	100
1000	T00703	162m Tax Account - R&D Other Equity Based Comp (RSU, PSU)	100
1000	999500	WIP Offset	100
1100	610111	Small F&F <$1,000	100
1100	610130	Badging Expenses	100
1100	610200	Sm Equip $1,000-$5,0	100
1100	610201	Small Equip <$1,000	100
1100	610250	Security Equipment	100
1100	610300	Print/Repro/Copy Cst	100
1100	610400	Forms	100
1100	610500	Safety Supplies	100
1100	610600	Research Material	100
1100	610700	Samples	100
1100	T00101	162m Tax Account - Salaries & Wages	100
1100	T00102	162m Tax Account - Stock Option Expense (NQ)	100
1100	T00103	162m Tax Account - Other Equity Based Comp (RSU, PSU)	100
1100	T00104	GILTI Inclusion	100
1100	T00501	162m Tax Account - COGS Salaries & Wages	100
1100	T00502	162m Tax Account - COGS Stock Option Expense (NQ)	100
1100	T00503	162m Tax Account - COGS Other Equity Based Comp (RSU, PSU)	100
1100	T00701	162m Tax Account - R&D Salaries & Wages	100
1100	T00702	162m Tax Account - R&D Stock Option Expense (NQ)	100
1100	T00703	162m Tax Account - R&D Other Equity Based Comp (RSU, PSU)	100

Company 1100 does not have the 999500 account.  I'm trying to come up with a way that to find when an entity doesn't have an account and add it with a 0.00 balance. We have about 8 entities and about 1200 GL accounts that I would need to search all and ensure each entity has the same number of rows (all GLs).

My second question is how I can easily update the treat the tax accounts (accounts beginning with "T" above) as a number for formula purposes (I'm assuming some kind of Left operator to replace the T with a zero to do whatever I need to do.  What I'm not certain of is how to get the T back on without messing up any of the other GL accounts.

[Jonathan-Sherman · Accepted solution]

Hi @davidlocke,

Question 1: You could join against a lookup table (created from the original dataset) I'll look at the second part now...

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Jonathan-Sherman]

Hi @davidlocke,

Could you give an example of what you're trying to do with the second part?

Regards,
Jonathan

[davidlocke]

The example is in the same dataset above.  The basic gist is there are times when I'm doing formulas where I need the GL account to be a double.  For instance:

IF [G/L AC] In (505000, 599999, 550100, 822222) Or [G/L AC]>899999 Or Isnull([G/L AC]) THEN 0

ELSEIF [G/L AC]>=599999 AND [G/L AC] <= 822221 THEN -[Difference]

ELSEIF [G/L AC]>=822223 AND [G/L AC] <= 899219 THEN -[Difference]

ELSEIF [G/L AC]>=899221 AND [G/L AC] <= 899998 THEN -[Difference]

ELSE [Difference]

ENDIF

These accounts beginning with T will never be directly involved, but are included in the data.  I guess I could filter them out and then join them back in?

[Jonathan-Sherman]

I see what you mean now @davidlocke, i would filter out the "T transactions first, use your formula and union back together.

You could use: !StartsWith([A/C], 'T') as your formula in a custom filter

If this solves your issue please mark the answers as correct, if not let me know!

Regards,

Jonathan