Question: create a process download for alteryx that can do the PO splitting. I want to place a script in alteryx that will perform all the

create a process download for alteryx that can do the PO splitting. I want to place a script in alteryx that will perform all the required steps to come out with the right solution: Audit Purchase Orders to Find PO Splitting Finance/Audit Overview In most companies, employees with purchasing authority have a monetary limit on how much they can purchase without management authorization. For example, an IT manager may be able to purchase up to $50,000 in software and equipment. Any amount over that would require director approval. In practice, this means that the IT manager requires director approval for any Purchase Order (PO) with a purchase amount over $50,000. If a manager wants to purchase $75,000, they could try to get around this by issuing two POs to the same vendor totaling $75,000 but each being less than $50,000. This is a violation of corporate compliance rules known as PO Splitting. Objective Create a simple workflow to detect PO splitting. In this case we are looking for multiple POs to the same vendor within a three-day period. At this point we are not checking to see if the same buyer is issuing the POs. (It is possible that there is collusion between employees to hide the splitting so there would need to be further examination of the POs found by our analysis). Processes The input file has PO line-item detail. The first step is to remove inactive POs, eliminate fields specific to the PO line, and retain only one record for each PO. Then identify the vendors to whom multiple POs have been issued. Determine if there are any vendors where the interval between their consecutive transaction dates is 3 days or less. Output this list for further investigation. (i.e., generate an Excel file output of your findings) Data Dictionary Purchase Orders.xlsx Column Name Data Type Description POID Double Purchase Order ID BuyerName V_String PO originator (buyer) CloseDate Date PO close date CompanyID V_String Company division ID CreateDate DateTime Date PO created InvoicedAmt Double Total cash amount invoiced to data OrderedQuantity Double Quantity ordered (line item) IsClosedForInvoiced V_String PO Status IsClosedForReceiving V_String PO Status PurchAmt Double Total cash amount of invoice TranStatus V_String Transaction status (Open if field is empty) SalestaxAmt Double Sales tax on entire PO TranAmt Double Transaction amount TranDate Date Transaction date TranNo V_String Transaction number TranType V_String =Purchase Order (Standard) VendorID Double Vendor ID PaymentTerm V_String Payment terms DueDayOrMonth Double Due date PmtTermsID V_String Payment terms code VouchID Double Voucher ID (line item) POLineKey Double Identifier for PO Line (line item) POLineNo Double PO line number (line item) IsReceivingRequested Double How to receive item (line item) POLineStatus V_String PO Line Status (line item, but needed for initial cleansing if any line is active, the PO is active) TargetCompanyID V_String Varies by line item UnitCost Double Unit cost (line item) UnitMeasure V_String Unit measure (line item) Guidance, tips and hints: Step 1: - Using the Input Data function - Using Filter function, [POLineStatus]!='Inactive' - Using Unique function for POID Step 2: - Using Summarize function, group by VendorID, count VendorID - Using Filter function, Count >1- Inner join the result from step 1 and step 2 Step 3: - Using Sort function, VendorID and TranDate in Ascending order - Using Multi-Row Formular function, select Create New Field, named TEST_Tran_Date, group by VenderID, Expression = DATETIMEDIFF([Row+1:TranDate],[TranDate],"DAYS")- Using Multi-Row Formular function, select Create New Field, named PrevTEST_Tran_Date, group by VenderID, Expression = DATETIMEDIFF([TranDate],[Row-1:TranDate],"DAYS")- Using Filter function, customer filter as [TEST_Tran_Date]<=3 OR [PrevTEST_Tran_Date]<=3

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!