Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Conditional lookup for two sources

JorgeVazquez
7 - Meteor

Hi Community,

I wonder if you can help me with something.

 

I am working on a large workflow. I am trying to join to different tables (sources) BUT with a conditional join.

As an example, please look at the tables below. "Table 1" contains the value I want to compare, and the "Table 2" contains the value I want to bring into my data. In this case, for instance, for my value "5", the corresponding joined value should be "A", for "7",  "9" and "11", should be "B", and so on. As you can see, I need to compare my value in Table 1 with the lower and higher limits in Table 2, to bring the value that matches.

 

 

Table 1 Table 2  
     
Value Lower valueHogher valueTarget value
5 16A
7 712B
9 1318C
11 1924D
13 2530E
15 3136F
17    
19    
21    
23    
25    
27    
29    
31    
33    
35    

 

Do you have any clues about how can I do this in Aleteryx?

 

I am using In-DB in the most of the cases, so if there is a solution with In-DB tools would be much better, if not that is not a problem.

 

Thank you very much in advanced.

 

7 REPLIES 7
jdunkerley79
ACE Emeritus
ACE Emeritus

The simplest way I can think of in this case (assuming it is integer values) is to use a Generate Rows tool to expand Table 2.

 

You can then join easily.

 

Sample attached

JorgeVazquez
7 - Meteor

Thanks @jdunkerley79,

 

This is great.

 

In my case it is dates what I am trying to compare. Any clue on how could that work using dates?

 

Thanks!

jdunkerley79
ACE Emeritus
ACE Emeritus
You can use the datetimeadd function to increment. I am not in front of
Alteryx at the moment but will post a sample when I can.
Joe_Mako
12 - Quasar

How about something like the attached?

 

For some other approaches, see https://community.alteryx.com/t5/Data-Preparation-Blending/Using-Join-Tool-For-Conditional-Where-Cla...

JorgeVazquez
7 - Meteor

I got it now!

 

Thank you for taking the time for this.

JorgeVazquez
7 - Meteor

Thank you so much for this. I got it now!

JoanaTF
5 - Atom

Very useful workflow thanks for sharing!

Labels