Showing two tables with different named dates and attributes in one table

[WhatsUp]

Hi Guys,

I’m struggling with the presentation of 2 different Tables with the same attributes in one Table.

I do have a Table with  active Customers  , CustomerType and ProductCategory

DateMonth | CustomerType|Category | actualAmount

31.01.23| a | 1 | 28 000

31.01.23| b| 1 | 10 500

…

My second Table are Goal Values aggregated per month:

PLAN_DATE|Plan_CustomerType |Plan_Category| Plan_amount

31.01.23| a | 1 | 30 000

31.01.23| b| 1 | 10 000

31.01.23 |a| 2 | 15 000

31.01.23 |b| 2 | 17 000

20.02.23| a | 1 | 31 000

…

The second Table is an aggregated Table from Excel. The first one can be drilled down to each day. I want to create a Pivot Table with:

Date | CustomerType | Category | actualAmount | PlanAmount

The Problem is when I use DateMonth as Dimension I have 0 values for the Plan_amount. If I choose PLAN_DATE as Dimension I have 0 values for DateMonth. Can someone help me get to the resultl I want? Also, the customerType and Category are the same but with a different name. I only want one of them in the table, while both numbers Plan_amount and actualAmount split.

[neerajthakur · Accepted solution]

Try this:

FinalTable:

Load *, DateMonth as Date from Table1;

Outer Join

Load *,PLAN_DATE as Date,

Plan_CustomerType as CustomerType,

Plan_Category as Category

from Table2;

Drop Table Table1,Table2;

This will help you, if not replace Outer Join with Concatenate