Alteryx designer Discussions

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

Vlookup with a formula

Highlighted
8 - Asteroid

Hello,

 

I have two tables:

Main Table                                                                                Lookup Table

Date                   Close Price                                                Quarter        Start Date       End Date

2019-12-09              200                                                  QDEC 2019     2019-10-01      2019-12-31

2018-03-15              215                                                  QMAR 2020     2020-01-01      2020-03-31

2020-01-25              275                                                  QMAR 2018     2018-01-01      2018-03-31

                                                                                         QJUN 2020      2020-04-01     2020-06-30

                                                                                         Q......and so on

 

I would like to add a new column to the main table that populates each row with "Quarter" from the Lookup Table.

 

End result should look like this:         

Main Table - Modified                                                                      

Date                   Close Price           Quarter                                                    

2019-12-09              200                  QDEC 2019                                             

2018-03-15              215                  QMAR 2018                                                

2020-01-25              275                  QMAR 2020

 

So in excel one could have used a vlookup with a formula if Date is after Start Date and before end date, lookup value in Quarter. How do you achieve that here.

 

Thanks

Deepa

Highlighted
Alteryx Partner

Hi Deepa,

 

My solution would be to use the append tool and then a filter: 

 

[Date] >= [Start Date]
AND
[Date]<=[End Date ]

 

DiegoParker_0-1581099204999.png

 

Find the workbook attached.

 

Hope this helps If does, can I ask you to mark it as the solution? this will help other users to find it and will allow us to close the thread. Many thanks!

Best,
Diego

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@deepaprash ,

 

I recently published a Generate Date Rows macro (https://gallery.alteryx.com/#!app/Generate-Date-Rows/5e31f42a826fd30ffcbf2c14) that converts the lookup table to each of the days in the range.  You can simply join to the output of the macro and find your lookup.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels