Preppindata练习03｜2023: Week 3 - Targets for DSB

Input

Requirements

• Input the data

• For the transactions file:

  • Filter the transactions to just look at DSB (help)
    • These will be transactions that contain DSB in the Transaction Code field
      
      
      Solution: selcted [PD 2023 Wk 3 Input], added “clean up" step, selected [transaction code] - [Filter] - [Calculation…], inserted formulas: LEFT([Transaction Code],3) = “DSB” , then saved it.
      
      The result should look like this.
  • Rename the values in the Online or In-person field, Online of the 1 values and In-Person for the 2 values
    Solution: That is quite easy, just changed “numerous” type into “string” type first, then edited the values and renamed them seperately.
  • Change the date to be the quarter (help)
    
    Solution: selected [transaction date] - “…” - “Convert Dates” - “Quarter number”, quarter means season.
  • Sum the transaction values for each quarter and for each Type of Transaction (Online or In-Person) (help)
    
    Solution: added “aggregate” step, on the left side, selected [Settings] so that we can see all the fields here. Then dragged [transaction date] 、 [transaction date] into [Grouped Fields], dragged [sum value] into [Aggregated Fields].

• For the targets file:

  • Pivot the quarterly targets so we have a row for each Type of Transaction and each Quarter (help)
    
    Solution: added “pivot” step, selected “Colums to Rows”, moved “Q1”-“Q4” to [Pivoted Fields]
  • Rename the fields as [Quarter]
    
  • Remove the ‘Q’ from the quarter field and make the data type numeric (help)
    

• Join the two datasets together (help)

  • You may need more than one join clause!
    
    根据输出结果可知，最终输出的是8行，第一次join有32行，所以需要重新检查[Applied Join Calues]处哪里有问题，注意变量对应值的匹配，并且需要两个Calues才可以达到最终的结果。

• Remove unnecessary fields
  

• Calculate the Variance to Target for each row (help)
  
  计算变量[Variance to Target], 公式：[Value] - [Quarterly Target]

• Output the data
  

Output