How to create a column for offsetting accounts

[Vu_Doan]

Hi all,

I'm new to this community. Please kindly help to advise on my problem. My set of data is as follows:

No	Date	Code	Client code	Name	JE description	Account code	Debit	Credit
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	6425	3.000.000	0
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	11211	0	3.000.000
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	24111	300.000.000.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	64283	11.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	11211	0	300.000.011.000

I would like my output as follows:

No	Date	Code	Client code	Name	JE description	Account code	Offset account	Debit	Credit
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	6425	11211	3.000.000	0
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	11211	6425	0	3.000.000
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	24111	11211	300.000.000.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	64283	11211	11.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	11211	24111	0	300.000.000.000
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	11211	64283	0	11.000

Thank you very much!

Best regards,

[caltang]

Hi @Vu_Doan,

Kindly confirm the following:

1. Is that all from the raw data? 

2. Is the full-stop meant to be a comma? For example 11.000 -> That is 11k (11,000.00) right?

3. How can you ascertain that the account 64283 has a Credit of 11,000.00 whilst account 24111 has 300,000,000,000? 

If you can give more details that will be most helpful.

[Vu_Doan]

Hi @caltang

---

---

Please see my responses:

1. This is just part of the data. If you'd like, I can post the whole raw data.

2. Yes, it is. 

3. We would have to rely on the first column to identify a specific JE. JE no. 76 looks like this on paper:

Dr 24111

Dr 64283

Cr 11211

Then we know that the total amount recorded in Acc. 11211 must be equal to the amount recorded in both Acc. 24111 and Acc. 64283.

[binuacs]

@Vu_Doan What is the logic to calculate the offset account?

[caltang]

I'm not sure if this works, but can you test this?

[Vu_Doan]

Hi binuacs,

The logic is that we rely on column "No" as the unique JE identifier. Please see the illustrative table as belows:

For JE no. 1, it looks like this:

Dr Expense

Cr Cash

and JE no. 76:

Dr PPE

Dr Expense

Cr Cash

I hope this helps.

[Vu_Doan]

Hi caltang,

I've tried your flow. I think that you're really close, but there seems to be a bit issue. I noticed that some entries are not transformed correctly as belows:

Input

Output

I also attached the raw data. Do you have any further suggestions?

[caltang]

Hi @Vu_Doan,

Thanks for the sample dataset. My apologies, I was on holiday the past week and I went abroad to solve Alteryx use-cases in person. 

I'll look into your issue again this week when time permits, but I think you can use my workflow still and make some adjustments to meet those exception cases.

Regards,

Cal

[caltang]

Hi @Vu_Doan !

Really sorry for the late reply. I was sent abroad for a few weeks for work and had to rush for several trips in succession.

Please find the new workflow as follows. Try it and let me know.

Best,

Cal