how to merge 2 duplicated key in a field when loading table

[43918084]

I need to join 2 periods of tables to calculate the variance by CUS ID.  But the occurrence of CUS ID in each table is not unique.  As a result the combined table balance are multiplied by their occurrence  (Table 4 "WRONG xxx")

I would appreciate some guidance on how to combine duplicated rows in each table respectively before Join . Or if there is other wiser way to fix the problem.  Many thanks

Below is my load table content.   

BS impair	Grca Entity	Period Date 42	CUS ID	T0 bal	T1 bal
mvt	2675	12/31/2020	8018892067	65,491
mvt	2675	12/31/2020	8018892067	26,196
JOIN
mvt	2675	3/31/2020	8018892067		65,835
mvt	2675	3/31/2020	8018892067		26,334

Below is my expected and wrong result

		T0 Bal	T1 Bal	Diff
expected	8018892067	91,687	92,168	481
wrong XXX	8018892067	184,337	183,375	(962)

[morgankejerhag · Accepted solution]

It looks like you want to aggregate the tables and not join. Lets call the tables Table1 and Table 2.

TableTmp:
Load * from Table1.qvd (qvd);
concatenate (TableTmp) Load * from Table2.qvd (qvd);

Table:
Load
   [BS impair],
   [Grc Entity],
   [Period Date 42],
   [CUS ID],
   sum([T0 bal]) as [T0 bal],
   sum([T1 bal]) as [T1 bal]
resident TableTmp group by [BS impar],[Grc Entity],[Period Date 42],[CUS ID];
drop table TableTmp;

Then in a chart you can take [T1 bal]-[T0 bal]. The aggregation above could also be done in the chart if you just concatenate the tables as in the first step.

[43918084]

Thanks a lot for the brilliant guidance.