Finding the last date of the orders
- 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
Dear all,
As shown in the picture below I have one column which is column A contain the phone numbers of the customers and the columns from B to G are the days.
What I'm looking for is to have a workflow that give me as an output two columns:
First column is the Mobile numbers of the customers same as column A and the Second column is the Last date of Order which is the last cell of the row which is filled by anything.
Note that I have attached the picture of the required ouput.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Feras95p
Here is how you can do it.
Workflow:
1. Using transpose tool to convert columns to rows
2. Using formula tool to convert dates into alteryx supported dates.
3. Using filter tool to filter blanks.
4. Using summarize tool for getting max date for each mobile number.
5. Using formula tool to get the desired date format.
Hope this helps : )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Feras95p ,
Here's one way to do it by transposing you data, keeping only those that don't have null values and then for each mobile number you keep the last one
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Nicely done @AngelosPachis 😎 ! There is always something new to learn from you.
Using the Last aggregation option in summarize. One of the underused and overlooked but powerful options. I definitely will start using it more 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @atcodedog05 , one thing to keep in mind here is that the solution I proposed will only work properly if the columns are a priori arranged chronologically in the Excel spreadsheet. If not, then the "Last" function of the summarize tool will not know which column is last in terms of time, but rather it will pick the one that's last in the order it appears in that spreadsheet.
Parsing the column headers into a date format (like you suggested ) will always work and return you the last time a call was made. Shooting myself in the foot here, but we got to be honest 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Great ! Thank you!
One more question in the date I have mixed up between the days and the months like 25/3/2021 how can I fix it to be dd/m/y because it's not giving me correct format of date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Feras95p
My formula should be able to handle 25/3/2021. If you provide us more sample data we can look into it.
