Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
RÉSOLU

Filtering data based on alphanumeric numbers in a different file

Bhavika
Astéroïde

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                                      

CodeAmount
FBXY100
FBXYUVTR200
FBXYWTRD50
FBXYWTRA20

 

Table2

ConditionFrom_CodeTo_Code
BetweenFBXYFBXYWTRD

 

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.

4 RÉPONSES 4
jdunkerley79
ACE Emeritus
ACE Emeritus

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
Astéroïde

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
ACE Emeritus
ACE Emeritus
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
Astéroïde

Thank you. This helps a lot. :)

Étiquettes
Auteurs des meilleures solutions