monthly sales history report - given 12 monthly sales by product files

cwoolivecwoolive Member Posts: 4 Contributor I
edited November 2018 in Help

I have  12  files . each  file  has three  attributes   a product  code , product  description and  the  quantity  of units sold for each product .


Would like to merge all 12 months  so that a given  product code  i can see the monthly sales for the last 12 months for each product  for a single record .



The  problem is some months have products not sold in the previous month hence it is difficult to  print out the product description when i use the join  outer function  .  in this  case  the  product  description is balnk since  on the left  set  nothing was sold so  blank  description and  product code.


Would  like  

product code    product  description             JAN     FEB  MAR   APR  MAY   JUN  JUL  AUG  SEP OCT  NOV  DEC           

pc001                       peanuts                          20       10     0           5      3         3     4         10    5      6      8         10

pc 002                      HATS                             10                              20                                   40                              50 


Best Answer

  • Options
    MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,520 RM Data Scientist
    Solution Accepted


    did you try to append the 12 files first to use pivot on it afterwards.



    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany


  • Options
    Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn

    And the pivot step may not even be necessary.  For many processes, this is not a required (or even a useful) step.  So depending on what you actually want to do with the data afterwards, you may be better off appending all 12 files together and then just leaving it in the format:

    Product code      Product Description    Sales    Month/Year


    This will give you a long & skinny table rather than a short and wide table.  The former is better suited  to a lot of data and analytical processing.  You can read more on this idea here: http://www.statsblogs.com/2016/04/25/spreadsheet-thinking-vs-database-thinking/


    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts
  • Options
    cwoolivecwoolive Member Posts: 4 Contributor I

    It worked , however i had to do a join  with  origianal  appended file to fill in the description

Sign In or Register to comment.