topic Re: expression: get value from row where x=y in QlikView

expression: get value from row where x=y

ni_avoss — Tue, 19 Feb 2013 17:52:55 GMT

hi there,

i need to peek some data within one table and don't know how to continue. Explains best on an example:

Product	ProductGroup	Units	UnitsGroup
GroupA	GroupA	30	30
A	GroupA	10	30
B	GroupA	10	30
C	GroupA	10	30
GroupD	GroupD	40	40
D	GroupD	20	40
E	GroupD	20	40

ok, so in one column(UnitsGroup) I want to see the Values for a Group and my approach is looking for values where Product=ProductGroup

I start with =if(Product=ProductGroup,sum(Units),)

and now I am stucked with the ELSE

any ideas?

thanks in advance and best regards

Re: expression: get value from row where x=y

Gysbert_Wassenaar — Tue, 19 Feb 2013 17:57:16 GMT

How about sum(total <ProductGroup> Units)/2 instead of your if statement?

Re: expression: get value from row where x=y

ni_avoss — Wed, 20 Feb 2013 15:01:09 GMT

hi, thanks for you reply.

well, your idea is good.
but my example wasn't that good, as the sum of a group consist also of data, which is not in the table, why "/2" doesnt work. lets assume in my given example UnitsGroup are not 30 and 40 but 50 and 70 or so...

Re: expression: get value from row where x=y

Gysbert_Wassenaar — Wed, 20 Feb 2013 15:05:52 GMT

But UnitsGroup will always be the maximum value over the ProductGroup? If so use this: max(total <ProductGroup> Units)

Re: expression: get value from row where x=y

ni_avoss — Wed, 20 Feb 2013 15:17:33 GMT

well, this might sound a little bit strange but no, necessarily it's not the maximum (due to other reasons).

well, I finally found a very specific workaround.

But I would be interesited anyway, if there is some solution or possibility like "peek" in an expression