Sum of amount

[#1]

Hi Everyone,

I have two fields Department1 and Department 2 with the same field values. The tabel looks like this

Department1	Amount
A	10
B	20
C	12
D	13
E	15

Department 2	Amount
A	12
B	12
C	14
D	32
E	26
F	25
G	12
H	19
I	18

My requirement is to get the rolled up amount for departments in Department1 from the amount associated with it department2.

For example in Department1 (A+B+C+D+E)= (10+20+12+13+15)= 70 but it should be (12+12+14+32+26) = 96 i.e. the amounts from department2 for each department in department 1.

How can i do this ?

[IAMDV]

Hi Sumit,

ApplyMap() is your friend. Have you tried using simple ApplyMap with Mapping Load. See the sample script and attached example.

/*----------------------------------------------------------------------------------------------------------------*/

Map_Table2:

Mapping LOAD * INLINE [

    Department, Amount

    A, 12

    B, 12

    C, 14

    D, 32

    E, 26

    F, 25

    G, 12

    H, 19

    I, 18

];

Temp:

LOAD * INLINE [

    Department1, Amount

    A, 10

    B, 20

    C, 12

    D, 13

    E, 15

];

Final:

LOAD *,

ApplyMap('Map_Table2', Department1, NULL()) AS Amount_From_Department2

Resident Temp;

Drop Table Temp;

/*----------------------------------------------------------------------------------------------------------------*/

Good luck!!

Cheers,

DV

[#3 · Accepted solution]

Thanks everyone for your response.

I solved the problem. I used the following expression

sum({$<Deptartment2={$(vDeptReimburse)}>} Amount)

where

vDeptReimburse = Chr(39) & Concat(DISTINCT Department1,Chr(39) & ',' & Chr(39)) & Chr(39)

Thanks again,

Sumit