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

cwoolive

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 

colin

MartinLiebig

Hi,

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

 

~Martin

Telcontar120

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/

 

cwoolive

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