Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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;
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;