Filtering data based on alphanumeric numbers in a different file

[Bhavika]

Hi there,

I'm in a situation where in i have 2 tables. 

Table1 is my master data and Table2 is a mapping table and can have different values in From_Code & To_Code. So, my workflow has to have a dynamic filter to deal with BETWEEN filter for alphanumeric values.

I have to filter on column CODE based on CODES that lie between 2 alphanumeric values residing in Table2.

Table1                                      

Code	Amount
FBXY	100
FBXYUVTR	200
FBXYWTRD	50
FBXYWTRA	20

Table2

Condition	From_Code	To_Code
Between	FBXY	FBXYWTRD

Now, i want to filter data that lies in between Code(FBXY till FBXYWTRD). 

I'm struggling to build a logic around this. Thought of JOIN control first, but couldn't figure out how JOIN would look like, since the starting value and final value of the BETWEEN range are in different columns. Thought of using a macro, but quite new to Alteryx so couldn't really start of with the idea.

Can someone please help me with this.

TIA.

[jdunkerley79]

Couple of options on this.

You can use and append fields to join Table 2 to Table 1. You can then use a filter tool on the joined the results. This works as long as Table 2 is a small list.

If a large list you can use a batch macro to run each filter in turn from Table 2 on Table 1.

I have attached a sample of the append fields solution.

[Bhavika]

Thank you for your reply.

I tried the same. However,the count of data in Table1 is about ~2M and in Table2 is ~300 unique combinations of BETWEEN condition codes. Could you please share a sample batch macro for the same. As i mentioned before, i am quite new to Alteryx and new to Alteryx macros as well.

Thank you.

[jdunkerley79 · Accepted solution]

I have attached a sample doing both at the scale you described. Appending fields took about 4 mins on mine and batch macro took about 6 with real scale. Have attached truncated (2000 x 300) sample

[Bhavika]

Thank you. This helps a lot. :)