Lookup Source Table Create New Fields

[knnwndlm]

Hello,

I have the following tables:

MAIN TABLE
MAT TYPE     POST TYPE    Created Date
ABC115         GED343            1/31/2020
ADD120         FFD445             4/25/2018
GED343         OID793             6/3/2017
FFD445          ADD120            3/2/2016
OID793          GED343            6/15/2017

SOURCE TABLE
Type        Start Date      End Date      Rate
GED343  2013-01-01   2016-12-31   0.09
GED343  2017-01-01   2017-06-30   0.0875
GED343  2017-01-07   2017-09-30   0.0925
GED343  2017-01-10   2020-06-30   0.095
GED343  2020-01-07   2021-03-31   0.0925
GED343  2021-01-04   2022-12-31   0.08
FFD445   2013-01-01   2016-12-31   0.0925
FFD445  2017-01-01    2017-06-30   0.075
FFD445  2017-01-07    2017-09-30   0.095
FFD445  2017-01-10    2020-06-30   0.08
FFD445  2020-01-07    2021-03-31   0.095
FFD445  2021-01-04    2022-12-31   0.085
OID793  2013-01-01     2016-12-31   0.9
OID793  2017-01-01     2017-06-30   0.88
OID793  2017-01-07     2017-09-30   0.08
OID793  2017-01-10     2020-06-30   0.0925
OID793  2020-01-07     2021-03-31   0.0925
OID793  2021-01-04     2022-12-31   0.095
ABC115  2013-01-01    2016-12-31   0.05
ABC115  2017-01-01    2017-06-30   0.85
ABC115  2017-01-07    2017-09-30   0.0655
ABC115  2017-01-10    2020-06-30   0.0456
ABC115  2020-01-07    2021-03-31   0.075
ABC115  2021-01-04    2022-12-31   0.055

RESULT TABLE
MAT TYPE    POST TYPE    Created Date    MAT RATE    POST RATE
ABC115        GED343            1/31/2020            0.075            0.0925
ADD120        FFD445            4/25/2018                                 0.08
GED343        OID793            6/3/2017               0.095            0.88
FFD445         ADD120           3/2/2016               0.0925
OID793         GED343           6/15/2017             0.08              0.09

The Main Table looks up the value in the Source Table to create the fields MAT RATE based on the look up value in MAT TYPE and POST RATE based on POST TYPE.  The criteria is to make sure that the Created Date falls within the Start and End Dates.

How can I do this so that I won't get a table that multiplies out the value?  In other words, I expect my result table to show only 6 records, nothing more. Any suggestions?  Also, is there a way that I can insert tables on this platform?  I kept having to put everything in plain text and manually manipulating the columns.

Many thanks,

Konn

[Qiu]

@knnwndlm 
I am not sure I fully understand your intention here, but I feel some constrains are missing, since I am getting more entries.

[knnwndlm]

Thanks for the reply.  I did something similar on a huge dataset over a 1M rows and got the same issues.  Not sure why that's the case.  In Excel, it would be a simple calculation with vlookup.

[knnwndlm]

You were right.  There's a problem with the Source Table.  It has multiple values under Type that contributes to the multiplying effects.

[Qiu · Accepted solution]

@knnwndlm 
I think in VLoopup it will pick the first occurence, which can do it with Sample too in alteryx also.