Finding the last date of the orders

[Feras95p]

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.  

[atcodedog05 · Accepted solution]

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

[AngelosPachis · Accepted solution]

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

[atcodedog05]

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 🙂

[AngelosPachis]

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 🙂

[Feras95p]

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

[atcodedog05]

Hi @Feras95p 

My formula should be able to handle 25/3/2021. If you provide us more sample data we can look into it.