How to combine group duplicate value in a field in initial Load script

[43918084]

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.

[mato32188 · Accepted solution]

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

[43918084]

thanks a lot.  This solution works perfectly.