2 input data, bringing in a column based off a common value

[JSheppard98]

Hi there,

I have 2 files as an example, both of which actually have around 100-200 rows. I am struggling to input these 2 files as raw input data and then I essentially want to vlookup in the "daily balances example" file (in a new column with the previous date), to the "prev day balances example" file on "account number", and pull through the balance/amount. 

I can obviously manually do this in excel with both files open and vlookup fine, but wondering if Alteryx can automatically do this for me and produce an output file of the daily balances example with this data populated?

Let me know if you need any more clarification? 

Thank you

[DenisZ]

I believe your issue could be solved using the Join tool. Join from both fields on something that is common for both field (account number). Then they would be on the side of each other. 

Hope this helps. 

[Felipe_Ribeir0]

Hi @JSheppard98 

Take a look at the attached workflow, it will work for you i think. The secret here is to use the dynamic select to filter the necessary fields (like the previous day date), and the join tool to join by the account number

[JSheppard98]

Hi Felipe, appreciate the swift help here.  I think this is good/close to working for my actual file as had a little play obviously but can't seem to get the join portion to work.

I have attached two more files of how the files would actually look if you wouldn't mind taking a look again please, some row's are null/would want to be blank because they have different "entity" headers. If this is easier, in the "Daily  Balances Example" file I could add a column in with just one same word, as to make the lookup/join work. If this would be a solution?

[Felipe_Ribeir0]

Hi @JSheppard98 

I can see that you have some different entities, but i could not understand the expected output for them.

[JSheppard98]

Hi Felipe, Apologies those would be just blank lines to separate different bank account entities, for example Ireland/Australia might be certain different "entities". Is that why the screenshot doesn't have the amount in the column, 2022-11-21? 

[Felipe_Ribeir0]

Hi @JSheppard98 

There is no amount on the columns 2022-11-21 because there is not amount on the input file:

[JSheppard98]

That is what I am looking to get from the "prev day balances" file. As the account numbers would be the same in both files, but I just want to populate that column 2022-11-21 with those corresponding amounts. If this is possible? Apologies for the confusion

[Felipe_Ribeir0 · Accepted solution]

Hi @JSheppard98 

Now i understood, you want the AMOUNT column from the prev day balances example.csv to populate this column, right? Here it is:

[JSheppard98]

Amazing, thank you for the continued effort this is spot on!