Solved: How to get data in pivot table format from multipl...

Amar-Agnihotri · ‎07-23-2022

Hi All,

I am having 5 excel files Sales_2017, Sales_2018, Sales_2019, Sales_2020,Sales_2021 and each excel is having this format of data

The customer Name may be same in any of the files and may be different and also it is possible that sopme customer are not present in any other files.

I want this format of data

The values in 2017, 2018, 2019, 2020, 2021 are the agregated sum of values present in each excel column.

For the customer who is not present in other years there will be null or blank.
First of All, I created a unique customer name table having all the customer names in that . I want to use this table as a Dimension Table.

Now i used merge between this new customer table with each of Sales table to get this format of data

I used the below join

So i performed 5 joins with each of the sales table to get these 5 tables

Till here the data is coming correctly. But now i am stuck to moved ahead. I am confused about what to perform now to get the below format -

If i perform join again between the above tables that i created then this will create multiple duplicate values in the table. We can't perform left outer join because it will loose those customer from the right table which are not in the left one. Same with the right join and if i perform full outer a lot of duplicates are bein created and we can't remove the duplicates from the final table because my source table is containing same customer multiple times with different sales values in the same year as shown

This is the source table Sales_2017 having multiple entries for the same customer

Please suggest how to achieve the requirement