Lookup and populate value within the same table

Hello all! I’m new to Alteryx and am still exploring its functionalities. I have a scenario that I have spent some time attempting with no success.

An excerpt of my data set looks as follows:

PNG 1.png

Value date is only assigned for ‘Type’ = KY. Therefore, for each ‘Type’ that is KR, I need to search the corresponding value date assigned to the KY document based on the clearing document number within the same table. I tried the multi-row formula but do not know the right expression to achieve the below result (especially when there are multiple clearing documents with the same value date):

PNG 2.png

Thank you!!

Expression

Accepted answers

jdunkerley79

I would suggest:

IIF([Type]="KR",[Row-1:Value Date],[Value Date])

I choose to update the exisitng field and set it to group by account (so it wouldnt copy across)

Sample attached

MultiRow Fill.yxmd

cmcclellan

The multi-row with the group by is the best option if your data is ordered.

If it's not ordered then something like this :

2018-11-29 19_08_24-Alteryx Designer x64 - New Workflow6_.png

Where you filter the KY records, summarize to get the date, then join by Document No to get the result (maybe a Select in there somewhere to clean up the output)

sunshine2

@cmcclellan, thank you for the workflow!

i realised the other way is also to use a filter and a find and replace right after.

PNG 3.PNG

All comments

ponraj

can you share the sample data and desired sample result in excel format ?

jdunkerley79

I would suggest:

IIF([Type]="KR",[Row-1:Value Date],[Value Date])

I choose to update the exisitng field and set it to group by account (so it wouldnt copy across)

Sample attached

MultiRow Fill.yxmd

cmcclellan

The multi-row with the group by is the best option if your data is ordered.

If it's not ordered then something like this :

2018-11-29 19_08_24-Alteryx Designer x64 - New Workflow6_.png

Where you filter the KY records, summarize to get the date, then join by Document No to get the result (maybe a Select in there somewhere to clean up the output)

jdunkerley79

@cmcclellan good point

You could also add a sort tool to order the data prior to the Multi Row if need - Sort Ascending By Account and Descending By Type

sunshine2

Thank you! This is great!

sunshine2

@cmcclellan, thank you for the workflow!

i realised the other way is also to use a filter and a find and replace right after.

PNG 3.PNG

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335