Monarch Exercise 33 - Exceedra Telus Trade Deduction Exercise

Baba_Majekodunmi_703
Baba_Majekodunmi_703
Altair Employee
edited January 25 in Altair RapidMiner

Exercise Info

  • Application (Classic vs Data Prep Studio): Classic & Data Prep Studio
  • Level of Difficulty (Beginner, Intermediate, Advanced): Advanced
  • Skills/Features Tested:  Standard Trap, Excel Worksheet Design, UnPivot
  • Systems of Record: Exceedra Telus
  • Estimated Time to Solve: 15 mins
  • Exercise files required are attached Below;
    • Classic Jan-Apr with UPC.txt
    • Classic Price List Worksheet.xlsx
    • Classic Deductions.xlsx
    • Image of Final Fields

Exercise Overview

You are an Trade Deductions Analyst at a Retail Brand Company and your Manager has asked you to capture trade deductions from the invoice provided by the Classic Wholesale Retailer and upload them to your Exceedra system. To perform this exercise you need to do the following:

1. Classic Jan-Apr with UPC.txt

  • Use Monarch Classic to create a model to extract data from the "Classic Jan-Apr with UPC.txt" file.
  • Deduction Amount = 6% of the Amount in this file, create that as a column.
  • Finally, import the classic model into data prep studio with the text file

2. Classic Price List Worksheet.xlsx

  • Next Import the "Classic Price List Worksheet.xlsx" into Data Prep Studio with Excel Worksheet Design
  • There are 3 columns for UPC, you need to create one column with all the UPC (hint: Unpivot)
  • Next, join the "Classic Price List Worksheet.xlsx" to the "Classic Jan-Apr with UPC.txt"

3. Classic Deductions.xlsx

  • Next import the "Classic Deductions.xlsx" file and join it to the prior to files.
  • Finally, you want to create and exported CSV file with the following columns from all the files
    • Report Date
    • Deduction Number
    • 1st 3 Letters of ERP Product Code
    • Reconciliation Code (Blank Column)
    • Reconciliation Status Action Code (Blank Column)
    • Invoice Number
    • Deduction Amount

Answer

  • Please upload your answers below in the comment section for the exercise
  • A Video Answer to the question will be published within 7-10 days on the Monarch Succcess Plan page in the 'Monarch Exercises' section.
Tagged:

Categories