Conparision tables for reconciliation

[EAK]

Hi everyone

I have 4 tables as stated below( table 1 is sunmary of other tables from other sources ) ı want to add new fields to table 1 as total of below table in order to compare the balances for correctness

thanks to All 

table1:

BS item, Total

Loan, 100

Cash, 150

Securities 75

TableLoan:

field1, field2,..etc, Total_ loan_balance

1 A…..,,,20

2 B…,,,80

TableCash:

field1, field2,..etc, Total_ Cash_balance

5 x…..,,,70

3 y…,,,60

9 z …., 20

TableSecurities;

field1, field2,..etc, Total_ security_balance

8 D…..,,,35

7 G…,,,40

[Mario_De_Felipe]

Hi @EAK ,

you have to aggregate  the three item tables and right join to table1 as follows:

RIGHT JOIN(Table1)
//TotalsTable:
LOAD
'Loan' as [BS item],
SUM(Total_ loan_balance) as Total_Field
RESIDENT TableLoan; // assumming you have loaded previously this table
concatenate
LOAD
'Cash' as [BS item],
SUM(Total_ Cash_balance) as Total_ Total_Field
RESIDENT TableCash;
concatenate
LOAD
'Securities ' as [BS item],
SUM(Total_ security_balance) as Total_ Total_Field
RESIDENT TableSecurities;

Hope it helps