Summarizing on two different tables

[DorryD]

Hello guys, i have the following question:

Imagine you have the three following tables.

A:
Load * Inline
[
Nation , Client
A , One
B , Two
];

B:
Load * Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];

C:
Load * Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];

I want to make a bar chart with dimension = date and measure should be a combination of table B and C. For example, the bar on date = 1 should be like 21 (from table B) + 40 (from table C).

If i select for example Nation = 'A', then of course the bar should be for Date = 1 

10 (from table B) + 20 (from table C) = 30.

I tried something like

Sum(Revenue) + if(Date = Date2, Sum(Revenue2))

but it doesnt work.

[MayilVahanan]

Hi

Try like below

MapClient:
Mapping
Load * Inline
[
Nation , Client
A , One
B , Two
];

MapNation:
Mapping
Load Client, Nation Inline
[
Nation , Client
A , One
B , Two
];

B:
Load *, ApplyMap('MapNation', Client) as Nation Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];

C:
Load Nation, Date2 as Date, Revenue2 as Revenue, ApplyMap('MapClient', Nation) as Client Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];

[mato32188]

Hi @DorryD ,

change your script a bit like following:

A:
Load Nation&Client as %DimKey, Nation, Client Inline
[
Nation , Client
A , One
B , Two
];

B:
Load * Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];

outer join(B)
C:
Load Nation, Date2 as Date, Revenue2 Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];

NoConcatenate
FACT:
Load
Nation&Client as %DimKey,
Date,
rangesum(Revenue,Revenue2) as Revenue
Resident B;

drop table B;

Use simple expression sum(Revenue) by any dimension, you like and it should be working.

BR

m

[simsa]

Hi DorryD,

Try this:

Sum(Revenue) + sum(if(Date = Date2, Revenue2))

[DorryD]

Hi Mayil Vahanan R,

this example is a simplification of my problem. Your approach works, but i'm searching for a solution where i don't have to change my data modell.

[DorryD]

Hi Mato,

this example is a simplification of my problem. Your approach works, but i'm searching for a solution where i don't have to change my data modell.

[DorryD]

Hi Simsa,

thanks for your help but unfortunately this solution doesn't work if you have more than one client per nation.