Create a new column and default values

[BobjiK]

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

[BobjiK]

Can someone please help

[binuacs]

@BobjiK can you provide the expected output?

[BobjiK]

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

[binuacs]

@BobjiK your due date format is in dd-mm-yyyy format and there is no date matching the current date ie 05-12-2023?

[BobjiK]

Hi @binuacs, currently there aren't any but you are free to add some lines.

[binuacs]

@BobjiK 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

[AndrewDMerrill · Accepted solution]

Not sure exactly what you are looking for either, but figured I'd share this to help further discussion:

[BobjiK]

Hi @AndrewDMerrill 

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.

[Qiu]

@BobjiK 

You need to replace the period "." as well