Joining Two Tables and applying where conditions

[USER132991]

Hello Everyone,

I am trying to find a solution to below situation where I need an output based on merging 2 tables and applying some condition as below:

Table1: 

IDs	Segment	Start Date	End Date
A	Yellow	1/1/2020	12/31/2020
A	Red	6/1/2019	12/31/2019
B	Blue	5/1/2018	12/31/2018

Table2:

ID	Date	Claims
A	8/1/2019	13
A	6/1/2020	145
B	1/1/2014	12

Final Output I want:

IDs	Segment	Claims
A	Yellow	145
A	Red	13
B	<null>	12

So basically the problem is like this: 

• I need an output which would tell me that for all IDs in Table 2 how many claims were falling in between the dates (Start Date and End Date) in Table 1 and then append their Segment from Table 1
• So, from the above tables for ID "B" since the "Date" in Table 2 is outside the range given for B in Table 1, therefore, it should display a blank in the "segment" column for B
• Table 2 is my parent table i.e. I want data for all the IDs in Table 2 and if any ID is present in Table 1 and not in Table 2 then we can ignore that

Hope I am able to explain this. What should be the best way to handle such things in Alteryx?

Thanks in advance!

[AbhilashR · Accepted solution]

Hi @USER132991, the approach in the attached solution is one way I think you can address your problem statement:

 

[USER132991]

Thank you. This has worked flawlessly 🙂

[StephV]

Hi @USER132991, 

Thank you for your question to the Community!

I moved your post to the Designer Discussions forum there you can ask anything technical about Designer. 

This article "Q&A: Where Do I Post My Questions?" should also help you out. 

Could you please mark @AbhilashR answer as "Solution" ? It will help other users. 

Thank you and have a very nice day!

Cheers,