Qlik Community

New to Qlik Sense

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

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
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 impairGrca EntityPeriod Date 42CUS IDT0 balT1 bal
mvt267512/31/2020801889206765,491 
mvt267512/31/2020801889206726,196 
      
JOIN     
mvt26753/31/20208018892067 65,835
mvt26753/31/20208018892067 26,334

 

Below is my expected and wrong result

  T0 BalT1 BalDiff
expected801889206791,68792,168481
     
wrong XXX8018892067184,337183,375(962)
Labels (1)
1 Solution

Accepted Solutions
morgankejerhag
Partner
Partner

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.

View solution in original post

2 Replies
morgankejerhag
Partner
Partner

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
Creator II
Creator II
Author

Thanks a lot for the brilliant guidance.