nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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,

Alteryx help.xlsx

Designer Cloud

Transformation

Join

Preparation

Accepted answers

caltang

Like so:

ForRyan.yxzp

All comments

caltang

Like so?

ForRyan.yxzp

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:

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.
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:

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

Output data browse.PNG

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

Like so:

ForRyan.yxzp

RyanDhaliwal

Great thanks for help- seems to work now!

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15910

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703