topic Re: Aggr in dimension does not return multiple values in QlikView

Aggr in dimension does not return multiple values

jduluc12 — Mon, 16 Jul 2018 00:08:09 GMT

Hi,

I am using Aggr in dimension like this

aggr( if( sum({<Type={'LA'}>}Amount) > 0, Name ), Address, Rep).

If there is more than one "Name" for the combination of "Address" and "Rep" then it returns NULL.

If there is only one "Name" for the combination of "Address" and "Rep" then it returns that value.

I also tried with NODISTINCT but it did not work.

What am i doing wrong?

Thanks,

Jean

Re: Aggr in dimension does not return multiple values

sunny_talwar — Mon, 16 Jul 2018 00:20:14 GMT

May be try this

Aggr(If(Sum({<Type={'LA'}>} Amount) > 0, Name), Name, Address, Rep)

or this

Aggr(If(Sum(TOTAL <Address, Rep> {<Type={'LA'}>} Amount) > 0, Name), Name, Address, Rep)

Re: Aggr in dimension does not return multiple values

jduluc12 — Mon, 16 Jul 2018 00:27:25 GMT

I tried the first one and it seems to be working.

Thanks.

So it seems that aggr can not return multiple values so you add name also in the dimensions so each value gets returned becasue then it will be only one value per name,

Re: Aggr in dimension does not return multiple values

sunny_talwar — Mon, 16 Jul 2018 00:36:07 GMT

Yup, but keep in mind that now Sum({<Type={'LA'}>} Amount) > 0 is also evaluated at Name (in addition to Address and Rep) level vs. just checking Sum({<Type={'LA'}>} Amount) > 0 against Address and Rep.

Re: Aggr in dimension does not return multiple values

jduluc12 — Mon, 16 Jul 2018 01:08:59 GMT

Yeah, I got that and it works for this one

but

what if i need to include only Address and Rep

is it possible?

Re: Aggr in dimension does not return multiple values

sunny_talwar — Mon, 16 Jul 2018 01:35:06 GMT

What do you mean include only Address and Rep? Not sure I understand?

Re: Aggr in dimension does not return multiple values

jduluc12 — Tue, 17 Jul 2018 00:10:48 GMT

I was responding to your comment

"Yup, but keep in mind that now Sum({<Type={'LA'}>} Amount) > 0 is also evaluated at Name (in addition to Address and Rep) level vs. just checking Sum({<Type={'LA'}>} Amount) > 0 against Address and Rep."

Right now the expression is getting evaluated at Name level too.

So I was asking what if i do not want to evaluate the expression at the Name level and just at Address and rep level but still include all names i.e. Aggr() should return multiple values.

Is it even possible?