Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Join Tool don't recognize every Account number

leonardo_okada
6 - Meteoroid

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.

 

leonardo_okada_1-1668455297417.png

leonardo_okada_2-1668455312730.png

 

 

 

leonardo_okada_0-1668455278426.png

 

8 REPLIES 8
Felipe_Ribeir0
16 - Nebula

Hi @leonardo_okada 

 

you must attach the inputs too

BrandonB
Alteryx
Alteryx

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

leonardo_okada
6 - Meteoroid

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_0-1668463357770.png

 

leonardo_okada
6 - Meteoroid

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
Alteryx
Alteryx

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
11 - Bolide

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

 

DenisZ_0-1668606580444.png

 

 

 

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
6 - Meteoroid

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
6 - Meteoroid

Hi,

That was the solution!

 

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

 

Thanks a lot.

 

leonardo_okada_0-1668620315745.png

 

Labels