topic Re: Aggr expression issue in QlikView

Aggr expression issue

jduluc12 — Thu, 04 Oct 2018 17:38:40 GMT

Hi,

I got an expression. A part of it is working.

I just want to know if i am violating an rules here.

if (ValueList('0', '1', '2', '3', '4') = '0',

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 0, 1)),

if (ValueList('0', '1', '2', '3', '4') = '1',

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 1, 1))

))

The dimension is a value list dimension. there are 5 dimensions (0 to 4) but I am using only 2 in this expression.

The main expression will be repeated for each dimension.

The main expression is:

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1])

If the value of sum is 0 then it has to go in first dimension and if the value is 1 then it has to show up for the second dimension.

This expression works for the first dimension but does not work for the second dimension. It does not show any value.

I am not sure what is going wrong with it. Is it wrong to have "aggr" statements used twice in these kind of expressions?

Thanks

Jean

Re: Aggr expression issue

vishsaggi — Thu, 04 Oct 2018 21:04:50 GMT

Your expr seems right. Can you share some sample data to look into or a qvw file you are working on?

Re: Aggr expression issue

jduluc12 — Fri, 05 Oct 2018 03:08:42 GMT

I will try to build some sample data.

Re: Aggr expression issue

andrey_krylov — Fri, 05 Oct 2018 18:02:18 GMT

As I understood there are some nuances in combining valuelists and aggregation. Try to add a NODISTINCT

if (ValueList('0', '1', '2', '3', '4') = '0',

sum(if(aggr(NODISTINCT count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 0, 1)),

if (ValueList('0', '1', '2', '3', '4') = '1',

sum(if(aggr(NODISTINCT count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 1, 1))

))

And see this post

https://qliktech.hosted.jivesoftware.com/message/851813#851813

Re: Aggr expression issue

jduluc12 — Fri, 05 Oct 2018 18:11:57 GMT

the second dimension shows up but it seems to bring the wrong data.

Let me read more about nodistinct .

Re: Aggr expression issue

andrey_krylov — Fri, 05 Oct 2018 18:28:45 GMT

See these

Re: Referring from aggr() to ValueList() value

Re: Aggr function on Valuelist

It seems you'd better to replace a valuelist with an island table (dimension).

Re: Aggr expression issue

jduluc12 — Sat, 06 Oct 2018 03:29:54 GMT

Now I am using this dimension

aggr(

if(count(DISTINCT{<ATxn.aType={'ABC'}},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

[ATxn.ID]) = 0, Dual('0', 1),

if (count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

[ATxn.ID]) = 1), Dual('1',2)) ,

[ARN.ID_1])

and expression is

count(DISTINCT{<[ARN.Type]={'Order'}>}

if (Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235

[ARN.ID_1]))

But it still shows nothing.

Any idea, if there is anything wrong with it.

Re: Aggr expression issue

andrey_krylov — Sat, 06 Oct 2018 05:19:21 GMT

I think the idea is to load an island table

LOAD * Inline [

DummyDim

];

then use it nstead of a ValueList and aggrigate by this dimension

if (DummyDim = 0,

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1], DummyDim) = 0, 1)),

if (DummyDim = 1,

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1], DummyDim) = 1, 1))

))

Re: Aggr expression issue

jduluc12 — Sat, 06 Oct 2018 17:44:31 GMT

Thanks to helping me out on this.

I learned something new.