Lookup and populate value within the same table

[sunshine2]

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:

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):

Thank you!!

[ponraj]

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

[jdunkerley79 · Accepted solution]

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

[cmcclellan · Accepted solution]

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 :

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

[cmcclellan]

Definitely :)   I didn't think of sort :)

I was actually working on my solution when you posted it, so I still wanted to post mine.  Your solution is more elegant though and that's what I like about Alteryx - having the most streamlined, easy to follow workflow :)

[sunshine2]

Thank you! This is great!

[sunshine2 · Accepted solution]

@cmcclellan, thank you for the workflow!

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