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
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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.

View solution in original post

43918084
Creator II
Creator II
Author

Thanks a lot for the brilliant guidance.