topic Re: summing columns in joined tables in Qlik Sense in App Development

summing columns in joined tables in Qlik Sense

Modiz — Sun, 16 Apr 2023 20:20:56 GMT

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" ?

Re: summing columns in joined tables in Qlik Sense

E_Røse — Sun, 16 Apr 2023 21:36:07 GMT

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.

Re: summing columns in joined tables in Qlik Sense

BrunPierre — Sun, 16 Apr 2023 22:07:18 GMT

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;

Re: summing columns in joined tables in Qlik Sense

E_Røse — Sun, 16 Apr 2023 22:31:05 GMT

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.

Re: summing columns in joined tables in Qlik Sense

Modiz — Mon, 17 Apr 2023 10:15:52 GMT

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. 😞

Re: summing columns in joined tables in Qlik Sense

brilling23 — Mon, 17 Apr 2023 10:27:31 GMT

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