🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.

Join and Win

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

Find more posts tagged with

AI Studio

Accepted answers

MartinLiebig

Hi,

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

~Martin

All comments

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