Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

What NODISTINCT parameter does in AGGR function?

Hi,

I try to figure out what the NODISTINCT parameter does in the AGGR function. When do we need to use it?

Do you have some clear examaple for this?

Thanks,

Guy

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

I did a little research on this issue, and that's what i found out:

Let assume the bellow data set:

Country   MonthProdMetric
IS1A140
IS1A250
IR1A330
IR2A110

I want to use a AGGR in order to consider the sum by Country and Month only, and let it be presented in a chart with Country, Month and Prod dimensions.

As you can see, the  =AGGR(SUM(Metric),Country,Month) will not fill all the rows data, because the AGGR prepare a data set like this:

Country   MonthMetric
IS190
IR130
IR210

and when QlikView try to use this set according to the Chart dimensions, it finds out it lacks information about the Prod Dimension, so it fills 90 only in the first row of the combination Country=IS and Month=1. The other row with the same combination gets Null ( - )

Country Month Prod Simple SUM =AGGR(SUM(Metric),Country,Month) =AGGR(NODISTINCT SUM(Metric),Country,Month)
IS1A1409090
IS1A250-90
IR1A3303030
IR2A1101010

We can Solve it by adding NODISTINCT qualifier to the AGGR function, which causes the data to be replicated for the same combination (see above).

I hope it helps!

Guy

View solution in original post

3 Replies
swuehl
MVP
MVP

Guy,

I assume you've read the description in the Help, so I am not going to copy it here.

Maybe this thread is of any help (if you follow the links to Eva's other posts, you see also some examples).

http://community.qlik.com/message/222026

Regards,

Stefan

edit:

This is the link to Evas thread including the sample file:

http://community.qlik.com/message/221791

christian77
Partner - Specialist
Partner - Specialist

Hi:

Sometimes aggr funcktion fills only some rows. If it does not work as expected, add nodistinct and it will.

If you started with nodistinct, and it doesn't work, take it away and see the result.

That's what I do.

The reason is that some rows have more than a single value and it retunrs null.

Not applicable
Author

Hi,

I did a little research on this issue, and that's what i found out:

Let assume the bellow data set:

Country   MonthProdMetric
IS1A140
IS1A250
IR1A330
IR2A110

I want to use a AGGR in order to consider the sum by Country and Month only, and let it be presented in a chart with Country, Month and Prod dimensions.

As you can see, the  =AGGR(SUM(Metric),Country,Month) will not fill all the rows data, because the AGGR prepare a data set like this:

Country   MonthMetric
IS190
IR130
IR210

and when QlikView try to use this set according to the Chart dimensions, it finds out it lacks information about the Prod Dimension, so it fills 90 only in the first row of the combination Country=IS and Month=1. The other row with the same combination gets Null ( - )

Country Month Prod Simple SUM =AGGR(SUM(Metric),Country,Month) =AGGR(NODISTINCT SUM(Metric),Country,Month)
IS1A1409090
IS1A250-90
IR1A3303030
IR2A1101010

We can Solve it by adding NODISTINCT qualifier to the AGGR function, which causes the data to be replicated for the same combination (see above).

I hope it helps!

Guy