Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to join below 2 tables together in load script to calculate the different balance of the 2 period.
But since the 1st table has 2 records of the CUS ID and 2nd table has another 2 records, the combined balance are duplicated
Load table logic
Period Date 42 | CUS ID | T0 bal | T1 bal |
12/31/2020 | 8018892067 | 65,491 | |
12/31/2020 | 8018892067 | 26,196 | |
JOIN | |||
3/31/2020 | 8018892067 | 65,835 | |
3/31/2020 | 8018892067 | 26,334 |
T0 Bal | T1 Bal | Diff | ||
expected | 8018892067 | 91,687 | 92,168 | 481 |
wrong XX | 8018892067 | 184,337 | 183,375 | (962) |
May I know if I can group the 2 duplicated CUS ID in first table and the 2nd table, respectively first before JOIN ? Or if there is any way I can fix this problem? Many thanks.
Hi,
what about this?
tmp1:
Load
[Period Date 42],
[CUS ID],
sum([T0 bal] as [T0 Bal]
from table 1
group by
[Period Date 42],
[CUS ID];
tmp2:
Load
[Period Date 42],
[CUS ID],
sum([T1 bal] as [T1 Bal]
from table 2
group by
[Period Date 42],
[CUS ID];
noconcatenate
Fact:
Load * resident tmp1; outer join(FACT) Load * resident tmp2;
drop tables tmp1, tmp2;
BR
m
Hi,
what about this?
tmp1:
Load
[Period Date 42],
[CUS ID],
sum([T0 bal] as [T0 Bal]
from table 1
group by
[Period Date 42],
[CUS ID];
tmp2:
Load
[Period Date 42],
[CUS ID],
sum([T1 bal] as [T1 Bal]
from table 2
group by
[Period Date 42],
[CUS ID];
noconcatenate
Fact:
Load * resident tmp1; outer join(FACT) Load * resident tmp2;
drop tables tmp1, tmp2;
BR
m
thanks a lot. This solution works perfectly.