Joining two tables

[billuran]

Hello, I ave joining problem, I have two tables as stated below example:

Table 1: Data Table

Dept	Amount	Pay Period
AA	5	Date1
AA	4	Date2
AA	4	Date2
BB	3	Date1
BB	4	Date2

Table 2: Target Table

Dept	Target	Pay Period
AA	YYY	Date1
AA	YYY	Date2
AA	YYY	Date3
AA	ZZZ	Date1
AA	ZZZ	Date2

I need to associate each target with the above table, based on dept and PayPeriod.

I want o to be able always want to see the amount no matter what the target selected.

Here is is the structure i want: Would like 45 to populated all the way down, this is where i am struggling.

[aarkay29]

Try

sum(Total<Dept>Amount) as Expression

[arvind_patil]

Hi Bill,

Join Both Tables With the help of Key like below:

DEPT&'-'&[PAY PERIOD] as %KEY

And remove those field from second table

Thanks,

Arvind Patil

[Kushal_Chawda]

see this

Don't join - use Applymap instead

TargetMap:

mapping LOAD

          Dept&[Pay Period] as Key,

          Target

FROM table2;

Table1:

LOAD Dept,

          Amount,

          [Pay Period],

          applymap('TargetMap',Dept&[Pay Period],0) as Target

FROM table1;