Vlookup date columns in multiple inputs based on a dynamic formula input

Question

Hi,

I have a workflow with multiple inputs:

1. Input A

CurrencyAccountNameDate T-1 (19-Dec-2022)Date T (20-Dec-2022)Date T+1 (21-Dec-2022)Date T+2Date T+3Date T+4

2. Input B

CurrencyAccountNameDate T-1 (19/12/2022)Date T (20/12/2022)Date T+1 (21/12/2022)Date T+2Date T+3Date T+4

Note that the dates in the above are dynamic - the input file gets saved each business day.

I have a separate formula where i have used a text input and formula tool to dynamically create the dates in the below Input C

DateT-1TT+1T+2T+3Dates19-Dec-202220-Dec-202221-Dec-202222-Dec-202223-Dec-2022

Can you advise what tools i can use, based on the dates in Input C, to only select the respective Date columns in Inputs A and B?

Thanks

Christina_H · Answer

Do the aliases match in all inputs?  (T, T+1, etc.)  I've used those to join the inputs together.

This works with this set of dates since they sort alphabetically.  For other sets you might need to find a way to sort the columns correctly.

Merge inputs on dates.yxmd

ShankerV · Answer

Hi @Sohdaw

There are some excellent posts about vlookup in knowledge.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-for-Excel-Users-How-to-do-a-VLOOKUP-in-Alteryx/ta-p/37953

Many thanks

Shanker V

Sohdaw · Answer

Hi  @ShankerV

Input A - note dates are consecutive including sat / sun.

CurrencyAccountNameDate T-1 (19-Dec-2022)Date T (20-Dec-2022)Date T+1 (21-Dec-2022)Date T+2Date T+3Date T+4THBxyzsdf123.34123.34123.34123.34123.34123.34THBadsffgs234.24234.243409.243409.243409.243409.24

Input B - note dates are consecutive including sat / sun.

CurrencyAccountNameDate T-1 (19/12/2022)Date T (20/12/2022)Date T+1 (21/12/2022)Date T+2Date T+3Date T+4THBdfepoo890.23890.23890.23890.23890.23890.23THBqwellk900.2323456.3423456.3423456.3423456.3423456.34

Input C - formula tool only picks up business days

DateT-1TT+1T+2T+3Dates19-Dec-202220-Dec-202221-Dec-202222-Dec-202223-Dec-2022

I have an additional mapping table where

AccountMapping xyzTEEadsfGERdfeTEEqweGER

Output - Dates are based on Input C

Mapping19-Dec-202220-Dec-202221-Dec-202222-Dec-202223-Dec-2022TEEsum of inputs A and B based on account and mapping (so eg. 123.34 + 890.23)    GER