Join Tool don't recognize every Account number

[leonardo_okada]

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.

[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).

[wdavis · Accepted solution]

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

[DenisZ]

You can press "metadata" in the top right corder of the results window to see the metadata.

I cannot see an obvious reason why it should not work. Initial thought is that the data types are different. Just use a select tool to make them both Double, Float or Integer and then join them together, should work then. 

Hope it helps. 

[leonardo_okada]

DenisZ, the account ID are in V_String type in both files so that's why it was really confusing.

Thanks for your help.

[leonardo_okada]

Hi,

That was the solution!

Just left the Join between the Account ID and remove the one for Amounts and worked.

Thanks a lot.