How to find the last non blank value in a row (similar to lookup function in excel)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi everyone,
I am currently trying to analyse a large data set of daily payments for individual clients (refer to attached file for a small sample of the data). The goal is to see the last payment date (if any) for each individual client. I have done this in the excel sample (with lookup value formula) and would like to replicate the results in Alteryx, in order to apply to the entire data set.
Any help would be much appreciated. Thank you in advance.
Atanas
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Naka!
I was able to replicate your results in the attached workflow. The key was to use a Transpose tool then summarize your data from there. I used a Join at the end to get all of the input data back.
Hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The Transpose tool help to transform the payment dates to rows to determine the total payments and last date. Then those values are joined back to the original data in my attached solution. Let me know if you have any questions.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for the reply, BarnesK. Unfortunately, I couldn't open the workflow as I am using an older version of Alteryx. I will try your solution, as soon as i update the software.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
CharlieS, this works perfectly. Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Naka!
For future reference, it's actually really easy to open a workflow that's in a newer version of Alteryx than the one you're using.
Simply right click on the workflow before opening it and select 'Edit with Notepad++.' From here, on line two , change the yxmdVer to the version of Alteryx you are using. Save, close, and viola. You should then be able to open the workflow without issues.
