I am working on a test scenario/procedure for split purchase orders such that I would like to create a flag for cluster of POs in the data tables based on multiple conditions as follows:
For each PO records, to check the records issued by the same Vendor ID within the 14 days window period
Total sum PO value within the 14 days window period by the same Vendor ID should be greater than 15,000 (each PO should be less than 15,000 in this exercise)
Assign a Cluster ID to the series of POs in order for the user to be able to deep dive into the details of these POs to determine whether it is a genuine case of split purchase (non-compliance risk of procurement)
Note that column C-G are the raw data available where I would like to create column A & B based on the above conditions
Raw data attached for reference.Assigning a flag based on conditions.csv (18.3 KB)
Many thanks in advance!