topic Re: Subquery possible? in QlikView

Subquery possible?

berkarmagan — Fri, 05 Jun 2015 13:39:20 GMT

Hi,

I m working on a pivot table and i ve a problem like this. First of all i ve a hierarchy of product group, each product group contains at least one product additional groups.My problem is I want to sum values of 3 rd and 4th add.group and Show in front of product group 1.

Product group	Prodcut Add Group	Value
1	1	10
1	2	20
1	3	30
2	4	30
2	5	40
3	6	50

Product Group	Calculation
1	sum(productaddgroup 4 and 5)
2	sum(productaddgroup 1 and 6)
3	sum(productaddgroup 2 and 3)

Re: Subquery possible?

marcus_sommer — Fri, 05 Jun 2015 16:11:59 GMT

It seems that your [Product Group] and [Product Add Group] aren't hierarchical but if they are you could use set analysis like:

sum({< [Product Add Group] = {4, 5}>} value)

- Marcus

Re: Subquery possible?

berkarmagan — Fri, 05 Jun 2015 22:51:44 GMT

Thanks for ur reply first but i need to get that result for Product group 1. In my pivot table i just have product group and calculated values. If i use directly sum({< [Product Add Group] = {4, 5}>} value) , i ll got the result like this becuse gropu 2 contains add group 4 and 5

Product Group	Calculation
2	70

according to that example i need this like below . On the db structure, Group 2 contains add group 4 and 5, but i need to Show this on the group 1's line and also I need to Show add group 1 and 6 on group 2's line. I think i sould use aggr() function to group them but i couldn't get sucsess.

Product Group	Calculation
1	30+40=70
2	10+50=60
3	30+30=60

Re: Subquery possible?

maxgro — Fri, 05 Jun 2015 23:31:18 GMT

if([Product group]=1, sum({$ <[Prodcut Add Group]={4,5}>} TOTAL Value),

if([Product group]=2, sum({$ <[Prodcut Add Group]={1,6}>} TOTAL Value),

if([Product group]=3, sum({$ <[Prodcut Add Group]={2,3}>} TOTAL Value)

)))

Re: Subquery possible?

berkarmagan — Sat, 06 Jun 2015 06:48:34 GMT

Thanks Massimo it worked but i think if we use 'total' in expression we lose grouping. I mean I ve got one more colon before product group as customer name. If I use "if([Product group]=1, sum({$ <[Prodcut Add Group]={4,5}>} TOTAL Value)" this it sum all values for all customers by product groups, but i need to get that calculation with grouping by Customer.

Thanks for your patience, I m kind of new on qlikview.

Re: Subquery possible?

berkarmagan — Sat, 06 Jun 2015 08:17:55 GMT

OK I think I found it, thanks for ur help at all. I can group my datas with total function like this:

if([Product group]=1, sum({$ <[Prodcut Add Group]={4,5}>} TOTAL <Customer> Value)

Thanks again!