Join Tool don't recognize every Account number

Hi all,

I've been trying to prepare a reconciliation beteween Journal Entries (.txt) vs TB (excel) but when I Join them there are many (500+) accounts that cannot be joined. However, almost 200+ lines were joined.

I used the Data Cleansing tool and the REGEX_REPLACE formula and still doesn't work.

Is there a way I can fix it? I've been searching through the main topics here but nothing seems to apply.

I also entered to every input file and every format is exactly the same.

I'm attaching te workflow so any of you can review it.

Reconciliation Test 2.yxmd

Join

Accepted answers

wdavis

Hi @leonardo_okada

Looking at your workflow the issue might be in that using the Join you are connecting based on both the 'Cuenta' Field and the 'Amount' field. Therefore, records need to be matching in both these instances.

For the reconciliation, could you just perform the Join based on 'Cuenta' to ensure that the account numbers match in both dataset.

Then, using a Formula Tool you can build in a reconciliation check

IF [Sum_JE Amount] = [BC Amount] THEN "Reconciled" ELSE "Not Reconciled" ENDIF

Let me know if that would work

Thanks

Will

All comments

Felipe_Ribeir0

Hi @leonardo_okada

you must attach the inputs too

BrandonB

You may also want to standardize everything to lowercase as well prior to the join as the join will be case sensitive.

leonardo_okada

I'm joining accounts numbers (Cuentas) in V_String type. No case in here.

Change data type, used the formula "REGEX_REPLACE([CUENTA],'[\x0-\x1f]','')", used the Data cleansing

I'm not able to upload the input data as it's sensitive information.

But I'm trying to figure out what are the options. Been doing my research and couldn't find much but the 2 options above.

In case you have seen similar cases and how they were solve I'd appreciate.

leonardo_okada

Hi Felipe

I'd like to share the files but are sensitive information.

I added an other reply with more explanation and a image of what I'm trying to join (account numbers).

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703