Hello Everyone
I am new to Alteryx development. Kindly help.
This is the requirement.
a. Create a new column called Priority.
b. Sort the due date in ascending order.
c. If the Due Date <= Current Date Then that row alongwith all the previous rows, plus, the rows that falls on the next working day, must be marked as "P1" in the column Priority.
d. The above must be only for those rows where the invoice amount >= 500.
I have given the data below
Please help.
Thanks
Bobji
Duplicate Group | Due Date | Invoice Amount | Priority |
191989 | 13-01-2021 | 4,556.00 | |
192012 | 17-03-2021 | 500.00 | |
192103 | 26-05-2021 | 342.00 | |
192044 | 11-09-2022 | 456.00 | |
192010 | 27-09-2022 | 5,670.00 | |
192019 | 02-11-2022 | 2,939.00 | |
191968 | 10-12-2022 | 3,949.00 | |
192005 | 30-12-2022 | 12.00 | |
192021 | 12-01-2023 | 444.00 | |
192024 | 20-01-2023 | 333.00 | |
191996 | 04-02-2023 | 5,959.00 | |
191971 | 04-05-2023 | 29,293.00 | |
191990 | 28-05-2023 | 3,98,484.00 | |
192019 | 17-06-2023 | 3,434.00 | |
191984 | 20-06-2023 | 7,878.00 | |
191991 | 27-06-2023 | 3,434.00 | |
191920 | 05-07-2023 | 67,776.00 | |
191913 | 17-07-2023 | 4,556.00 | |
191927 | 21-07-2023 | 500.00 | |
191927 | 26-07-2023 | 342.00 | |
191934 | 22-08-2023 | 456.00 | |
191980 | 25-08-2023 | 5,670.00 | |
192007 | 25-08-2023 | 2,939.00 | |
191987 | 29-08-2023 | 3,949.00 | |
191979 | 09-09-2023 | 12.00 | |
191983 | 16-09-2023 | 444.00 | |
192016 | 05-10-2023 | 333.00 | |
192026 | 09-10-2023 | 5,959.00 | |
192022 | 12-10-2023 | 29,293.00 | |
191942 | 14-10-2023 | 3,98,484.00 | |
191942 | 14-10-2023 | 3,434.00 | |
191937 | 14-10-2023 | 7,878.00 | |
191925 | 16-10-2023 | 3,434.00 | |
191939 | 16-10-2023 | 67,776.00 | |
191903 | 17-10-2023 | 4,556.00 | |
191902 | 17-10-2023 | 500.00 | |
191924 | 18-10-2023 | 342.00 | |
192046 | 21-10-2023 | 456.00 | |
191972 | 23-10-2023 | 5,670.00 | |
192006 | 24-10-2023 | 2,939.00 | |
191906 | 25-10-2023 | 3,949.00 | |
191868 | 28-10-2023 | 12.00 | |
191945 | 30-10-2023 | 444.00 | |
191945 | 30-10-2023 | 333.00 | |
191936 | 31-10-2023 | 5,959.00 | |
191974 | 31-10-2023 | 29,293.00 | |
191996 | 02-11-2023 | 3,98,484.00 | |
192043 | 03-11-2023 | 3,434.00 | |
191992 | 04-11-2023 | 7,878.00 |
Solved! Go to Solution.
Can someone please help
@Kallis can you provide the expected output?
Hi @binuacs
This is the output I require
Current Date = 12 Dec 2023
The dates given below are all in the past but the invoice amount is more than 500. Hence, the column is marked as P1. The rest as P2
Duplicate Group | Due Date | Invoice Amount | Priority |
191989 | 13-01-2021 | 4,556.00 | P1 |
192012 | 17-03-2021 | 500.00 | P1 |
192103 | 26-05-2021 | 342.00 | P2 |
192044 | 11-09-2022 | 456.00 | P2 |
192010 | 27-09-2022 | 5,670.00 | P1 |
192019 | 02-11-2022 | 2,939.00 | P1 |
191968 | 10-12-2022 | 3,949.00 | P1 |
192005 | 30-12-2022 | 12.00 | P2 |
192021 | 12-01-2023 | 444.00 | P2 |
@Kallis your due date format is in dd-mm-yyyy format and there is no date matching the current date ie 05-12-2023?
Hi @binuacs, currently there aren't any but you are free to add some lines.
@Kallis If I add the date 05/12/2023 and sort in ascending order, this date will be in the last of the list, according to your requirement 2 all the values which come above this date should be marked as P1 and P2 (only for amounts < 500)? I am still not understanding the ask, it would be very helpful if you can add today's date and provide another output
Hi @CoG
This WF, more or less solves my problem. However, I am getting a couple of errors while trying to execute it.
As you can see from the screenshot, the Sort Date returns Null and the Invoice Amount DBL returns an error
Formula: tried to apply string operator to numeric value (REPLACECHAR)
The data type for the 2 fields are
Invoice Amount Base Currency = Double
Due Date = Date
Kindly let me know how to solve this problem.
You need to replace the period "." as well