Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

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

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 42CUS IDT0 balT1 bal
12/31/2020801889206765,491 
12/31/2020801889206726,196 
    
JOIN   
3/31/20208018892067 65,835
3/31/20208018892067 26,334

 

  T0 BalT1 BalDiff
expected801889206791,68792,168481
     
wrong XX8018892067184,337183,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.

Labels (1)
1 Solution

Accepted Solutions
mato32188
Specialist
Specialist

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

ECG line chart is the most important visualization in your life.

View solution in original post

2 Replies
mato32188
Specialist
Specialist

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

ECG line chart is the most important visualization in your life.
43918084
Creator II
Creator II
Author

thanks a lot.  This solution works perfectly.