Vlookup based on two sets of criteria

[RyanDhaliwal]

Hi I am trying to join two datasets which are huge files.

Dataset 1- Unique account ID

Dataset 2- Unique account ID, unique date, Value

I want to pull in the data from Dataset 2 on value and date. See output required tab. 

Please see attached example of how my datasets look and output required. I have tried using find and replace however I am unable to get it right

Thanks in advance,

[caltang]

Like so?

[RyanDhaliwal]

Thanks! Two questions:

1) do the values need to be a double data type to work? In my actual data flow I have large values and when I use double it misses numbers off. When I change to V string the data flow does not work- a

2) When I apply to my data it is only pulling in one line of data for the full year , there should be different numbers matching across the year to different months- any tips?

[caltang]

To answer you:

1. The data type does matter - it matters since both entries into the LEFT and RIGHT inputs of the Join tool must be of the same type, otherwise it goes into an error. 
2. What you are describing is a many-to-one/one-to-many/many-to-many scenario. Do you mind giving some screencaptures or if you can, could you export the whole workflow over?

Here are some great articles from the Data School and the Information lab:

• https://www.thedataschool.co.uk/viktoriia-slaikovskaia/how-does-join-tool-works-in-alteryx/
• https://theinformationlab.nl/2022/05/06/different-types-of-joins-in-alteryx/ 

[RyanDhaliwal]

Thanks Caltang. I am unable to export the full workflow as has sensitive information in it. 

See screen captures below- taking one account ID as an example.

Input data- shows theres values which are across different periods

 

Output data-only pulls in one line of the data i.e. the -913.5

 

[caltang]

That's because I have the Unique tool there to prevent multiple copies. Is that what you desire? You can remove the Unique tool to achieve what you want. You can remove both of them.

[caltang · Accepted solution]

Like so:

[RyanDhaliwal]

Great thanks for help- seems to work now!