summing columns in joined tables in Qlik Sense

[Modiz]

Hi,

I need help with joining two tables. I have two tables:

date	product	quantity
01.04.2022	a	5
01.04.2022	b	6
01.04.2022	c	7
02.04.2022	a	2
02.04.2022	b	4

and:

product	range from	range to	price
a	1	50	2
a	51	100	3
a	101		4
b	1	100	2
b	101		3
c	1	50	1
c	51		2

In Qlik Sense I am making a report with a simple table:

prooduct	avg(price)	sum(quantity)
a	3	7
b	2,5	10
c	1,5	7

unfortunately, when summing the "quantity" column, I get duplicate values.

Could someone help me, how can I write an Set Expression that sum column "QUANTITY" ?

[E_Røse]

Don't join the tables. Use keep instead of join in your load script.

Please like and mark my answer as a solution, if it resolved your issue.

[BrunPierre]

As below.

NoConcatenate
Final:
LOAD product, 
     Max(date) as date, 
     Sum(quantity) as quantity
     
Resident Table1
Group by product;


Left Join (Final)
LOAD product, 
     Avg(price) as price
     
Resident Table2
Group by product;

DROP Tables Table1, Table2;
  
EXIT SCRIPT;

[E_Røse]

That would not work. You should create the measures in the Sheets. Just remove all the code you have there. The tables Table1 and Table 2 will be automatically associated. Then create measures avg(price) and sum(quantity) in a table in a sheet.

[Modiz]

Thanks, but I have one more question, would you like to help me? If table2 had one more column: date_of_range.

And I want sum(quantity) in a table with a condition: sum(if(date >=date_of_range, quantity,0)), this solution doesn't work. 😞

[brilling23]

If you organize and calculate sums of large amounts of data regularly  TellPopeyes