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: EntityA/CDescAmt1000610111Small F&F <$1,0001001000610130Badging Expenses1001000610200Sm Equip $1,000-$5,01001000610201Small Equip <$1,0001001000610250Security Equipment1001000610300Print/Repro/Copy Cst1001000610400Forms1001000610500Safety Supplies1001000610600Research Material1001000610700Samples1001000T00101162m Tax Account - Salaries & Wages1001000T00102162m Tax Account - Stock Option Expense (NQ)1001000T00103162m Tax Account - Other Equity Based Comp (RSU, PSU)1001000T00104GILTI Inclusion1001000T00501162m Tax Account - COGS Salaries & Wages1001000T00502162m Tax Account - COGS Stock Option Expense (NQ)1001000T00503162m Tax Account - COGS Other Equity Based Comp (RSU, PSU)1001000T00701162m Tax Account - R&D Salaries & Wages1001000T00702162m Tax Account - R&D Stock Option Expense (NQ)1001000T00703162m Tax Account - R&D Other Equity Based Comp (RSU, PSU)1001000999500WIP Offset1001100610111Small F&F <$1,0001001100610130Badging Expenses1001100610200Sm Equip $1,000-$5,01001100610201Small Equip <$1,0001001100610250Security Equipment1001100610300Print/Repro/Copy Cst1001100610400Forms1001100610500Safety Supplies1001100610600Research Material1001100610700Samples1001100T00101162m Tax Account - Salaries & Wages1001100T00102162m Tax Account - Stock Option Expense (NQ)1001100T00103162m Tax Account - Other Equity Based Comp (RSU, PSU)1001100T00104GILTI Inclusion1001100T00501162m Tax Account - COGS Salaries & Wages1001100T00502162m Tax Account - COGS Stock Option Expense (NQ)1001100T00503162m Tax Account - COGS Other Equity Based Comp (RSU, PSU)1001100T00701162m Tax Account - R&D Salaries & Wages1001100T00702162m Tax Account - R&D Stock Option Expense (NQ)1001100T00703162m 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.

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

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.

Datasets

Common Use Cases

Best Practices

Expression

Accepted answers

Jonathan-Sherman

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

20200428 - AddNonExistentGLAccount.yxmd

All comments

Jonathan-Sherman

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

20200428 - AddNonExistentGLAccount.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703