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 CodeAmountFBXY100FBXYUVTR200FBXYWTRD50FBXYWTRA20 Table2 ConditionFrom_CodeTo_CodeBetweenFBXYFBXYWTRD 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.

Filtering data based on alphanumeric numbers in a different file

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.

Macros

Join

Accepted answers

jdunkerley79

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

Between join 2ways.yxzp

All comments

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.

Append Filter.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703