Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
community Alteryx IO Mission Control
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

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

Vlookup Function

Myusrename001
8 - Asteroid
‎09-15-2024 11:39 AM

Hello Team,
There are 2 set of data coming from 2 different input files, now i wanted to club them and needed output as per below format. Can anyone please help me.
kindly note the pay date should consider based on the effective date or record date and date is in DD-mm-YYYY format.
This is sample file - we have multiple secuirty involved and needed a logic to find it. 

1st Input file :

FileNameCusipISINEFFECTIVE DATEOWNERSHIP %FURNISHEDNo of WHSTop / SubAccount
NQI WHS -_Record Date January 11,20231US110-01-20241 17TOP Account17
NQI WHS -_Record Date January 11,20231US110-01-20240.497826087W-8BEN-E17Sub Account17
NQI WHS -_Record Date January 11,20231US110-01-20240.436956522W-8BEN17Sub Account17
NQI WHS -_Record Date January 11,20231US110-01-20246.52E-02W-8BEN-E17Sub Account17
NQI WHS -_Record Date APR 15,20241US110-04-20241 16TOP Account16
NQI WHS -_Record Date APR 15,20241US110-04-20240.486956522W-8BEN-E16Sub Account16
NQI WHS -_Record Date APR 15,20241US110-04-20240.447826087W-8BEN16Sub Account16
NQI WHS -_Record Date APR 15,20241US110-04-20246.52E-02W-8BEN-E16Sub Account16

 

2nd Input file:

Pay DateRec DateCusipAmountNRA W/H  
02-01-24 125.53-7.66  
05-01-24 125.53-7.66  

 

Output file :

FileNameCusipISINEFFECTIVE DATEOWNERSHIP %FURNISHEDNo of WHSTop / SubAccountPay DateRec DateCusipAmountNRA W/H
NQI WHS -_Record Date January 11,20231US110-01-20241 17TOP Account1702-01-24 125.53-7.66
NQI WHS -_Record Date January 11,20231US110-01-20240.497826087W-8BEN-E17Sub Account1702-01-24 125.53-7.66
NQI WHS -_Record Date January 11,20231US110-01-20240.436956522W-8BEN17Sub Account1702-01-24 125.53-7.66
NQI WHS -_Record Date January 11,20231US110-01-20246.52E-02W-8BEN-E17Sub Account1702-01-24 125.53-7.66
NQI WHS -_Record Date APR 15,20241US110-04-20241 16TOP Account1605-01-24 125.53-7.66
NQI WHS -_Record Date APR 15,20241US110-04-20240.486956522W-8BEN-E16Sub Account1605-01-24 125.53-7.66
NQI WHS -_Record Date APR 15,20241US110-04-20240.447826087W-8BEN16Sub Account1605-01-24 125.53-7.66
NQI WHS -_Record Date APR 15,20241US110-04-20246.52E-02W-8BEN-E16Sub Account1605-01-24 125.53-7.66

 

Labels:
Reply
0 Likes
2 REPLIES 2
Bren_Spill
12 - Quasar
12 - Quasar
‎09-15-2024 12:20 PM

@Myusrename001 - how do you plan to identify which payment date should be applied to a record in the first input?

 

Attached uses an input table to tag the payment dates with the relevant file date. Refer to this for the date cleansing I did: DateTime Functions (alteryx.com)

Vlookup Dates.yxmd
Reply
TUSHAR050392
11 - Bolide
‎09-16-2024 08:50 PM

Hey @Myusrename001 I have the same question as @Bren_Spill. What are your match columns in both inputs? Once you have that figured out, you can refer to the documentation of Find Replace which is the Vlookup equivalent in Alteryx.

https://help.alteryx.com/current/en/designer/tools/join/find-replace-tool.html

 

Reply
0 Likes
view community events calendar
designer new user guide
Labels