Multiply 2 table with multiple conditions

[llky23]

Hi, I have 2 tables and I want to multiply them together as follows:

Table 1:

CC	Month	Year	GL	Text	Amt
8000100	1	2020	8000101	a1	10

Table 2:

CC	Prd CC	Month	Year	Ratio
8000100	199999	1	2020	50%
8000100	298999	1	2020	50%

I want to end result to be this table

Result_table:

CC	Month	Year	GL	Text	Amt	Prd CC	Final Amt	Ratio
8000100	1	2020	8000101	a1	10	199999	5	50%
8000100	1	2020	8000101	a1	10	298999	5	50%

The matching conditions between Table 1 and 2 are [CC] , [Month] , [Year]

How can I do this in script, thanks!!

[lironbaram · Accepted solution]

HI 

check this script 

Table1:
load * inline [
CC,	Month,	Year,	GL,	Text	,Amt
8000100,	1,	2020,	8000101,	a1,	10
];
left join (Table1)
load * inline [
CC,	PrdCC,	Month,	Year,	Ratio
8000100,	199999,	1,	2020,	0.5
8000100,	298999,	1,	2020,	0.5
];

FinalTable:
load *,
     Amt*Ratio as FinalAmt
resident Table1;

drop Table Table1;