Combining 2 data sheets based on a range between 2 columns

[ChristianJFH]

Hi 

I have 2 data sets

I want to combine them based on Entry number.
One file contains a column called Entry numbers, so thats all good.
The 2nd file contains 2 columns called From_Entry_Number and To_Entry_Number. 

If the Entry number from file 1 matches within the range from 2nd dataset From_Entry_Number - To_Entry_Number then i want to combine them
File one:

Entry Number:	User ID	Date
1
2
3
4

File 2:

From_Entry_Number	To_Entry_Number	Amount	Creation_date
1	2
3	5
6	9

If they do not match i do not want them in the combined file. There is a lot of columns that needs to be combined and relatively big data files (6million  records and 10 records)

[JulioMO]

Hi @ChristianJFH 

I attach a workflow that does what you asked. I have created a "Range" column to be able to joint both data sets.

Hope it helps!

Regards

[ChristianJFH]

HI @JulioMO

Thanks for the answer! This is helping me get closer. But writing manual formula with the range would not be an options since I have about 4m ranges 

[JulioMO]

Hi @ChristianJFH 

Then let's think the other way around. 

I have attached a second version that matches the range automatically. 

Let me know if that works for you.

Regards

[ChristianJFH]

Hi again @JulioMO

This was also a good idea, i tried it out but i had to stop it since the Append tool kept growing (stopped it at 900gb). I appriciate your effort! ill be logged off for today but will look into the issue again tomorrow

[JulioMO]

Hi @ChristianJFH 

Try this batch macro so you can free up some space in the append tool. 

Let me know if it works for you.

Regards

[ChristianJFH]

Hi @JulioMO
Sadly i could not get that one to work 😕 

I manage to solve this issue in ACL but i would really like to solve it in Alteryx aswell. 

What i did in ACL was making a new loop that created a line for every value in the loop (value from [From_entry_Number]<= and >=[To_entry_Number]

File 2:

From_Entry_Number	To_Entry_Number	Amount	Creation_date	Entry number Value
1	2			1
1	2			2
3	5			3
3	5			4
3	5			5
6	9			6
6	9			7
6	9			8
6	9			9

This way i could join the data based on "Entry_number" from data 1 and "Entry Number Value" from data 2
Now i just need to figure out how to do this step in alteryx 

[JulioMO · Accepted solution]

Hi @ChristianJFH 

You can do that with a simple tool called "Generate Rows." 

I attach the workflow to do it. 

Regards

[ChristianJFH]

Thank you @JulioMO 

Ended up being a waaay simpler fix than expected