# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for
Did you mean:
Creator II

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

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)
Labels (1)
• ### Qlik Sense

1 Solution

Accepted Solutions
Partner

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

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

Table:
[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.

2 Replies
Partner

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

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

Table:
[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.

Creator II
Author

Thanks a lot for the brilliant guidance.

Tags
Community Browser