topic Re: problem to use Aggr(count(Distinct)) in QlikView

problem to use Aggr(count(Distinct))

Anonymous — Tue, 14 Jul 2015 11:25:06 GMT

Hi, all,

I have a straight table as below:

Name subName Expression1 E2 E3

A A1 ...

A A2 ...

B B1 ...

- C1 ...

The purpose is to change subName based on some condition to get a table as below:

Name subName Expression1 E2 E3

A A1 ...

A A2 ...

B B ...

- C1 ...

The condition to change the subName is:

Name is not null

Aggr(count(distinct subName), Name)>1.

But I found that Aggr(...) can not work correctly for A2 case, when I use Aggr(...) as a calculated dimension (CD case), the Aggr(...) get correct result, but when I use if (Aggr(...)>1, subName, Name), the result is not correct. I also tried to test it by using Aggr(...) as an expression (E case), this dose not work as well. Like below:

Name subName Aggr(...) (CD case ) if (Aggr(...)>1, subName, Name) (CD case ) Aggr(...) (E case)

A A1 2 A1 2

A A2 2 A (hope A2, but it is A ) - (expect 2, but it is Null)

I have no idea how to improve this any more. And maybe I did not use Aggr(...) correctly, can some one help?

Attached is my example qvw file. Like mentioned above, my target it to get A2 in the 4th dimension column (in the .qvw file) with calculation condition "if (Aggr(...)>1, subName, Name)". Sorry to make confusion, the Aggr(...) in the expression column is for test usage.

Thanks very much

Re: problem to use Aggr(count(Distinct))

MK_QSL — Tue, 14 Jul 2015 11:29:30 GMT

It would be helpful if you upload a sample data/application along with your expected result.

Re: problem to use Aggr(count(Distinct))

Anonymous — Tue, 14 Jul 2015 12:01:53 GMT

Hi, Manish,

Thanks for you quick response. Already attached a .qvw example.

Zhihong

Re: problem to use Aggr(count(Distinct))

MK_QSL — Tue, 14 Jul 2015 12:05:34 GMT

What result you are expecting? Can you provide this ?

Re: problem to use Aggr(count(Distinct))

antoniotiman — Tue, 14 Jul 2015 12:06:37 GMT

Hi,

see attachment

Regards,

Antonio

Re: problem to use Aggr(count(Distinct))

Anonymous — Tue, 14 Jul 2015 12:31:56 GMT

Hi, Manish,

I already wrote in my question. I would like to get required SubName based on the Aggr(...) condition. And the problem is that I can not get A2 correctly.

Zhihong

Re: problem to use Aggr(count(Distinct))

jonathandienst — Tue, 14 Jul 2015 12:40:50 GMT

I don't think you need an Aggr() here. This should work if you want to replace the subName if there is only one, and Name is not null:

If(len(Name) > 0 And Count(Total <Name> Distinct subName) = 1, Name, SubName)

If(len(Name) > 0 And Count(Total <SubName> Distinct subName) = 1, Name, SubName)

Re: problem to use Aggr(count(Distinct))

Anonymous — Tue, 14 Jul 2015 12:45:20 GMT

Hi, Antonio,

It works now, You are my hero. I attached the correct solution here, which may save people time to go into the .qvw file.

=Aggr(If(Count(DISTINCT Aggr(Subname,Name)) > 1,Name,Subname),Name,Subname)

Thanks for all you quick reply.

Zhihong

Re: problem to use Aggr(count(Distinct))

MK_QSL — Tue, 14 Jul 2015 12:46:44 GMT

=IF(Aggr(COUNT(TOTAL <Name> DISTINCT SubName),Name,SubName)>1,SubName,Name)

Re: problem to use Aggr(count(Distinct))

Anonymous — Tue, 14 Jul 2015 12:57:22 GMT

Hi, Jonathan,

Thanks, your solution works in the expression column, but dose not work in the dimension column. Will get error:

//Error in calculated dimension

Though according to me, it seems correct, do you know why? Is that because the "Total" can bot be used in the dimension?

Re: problem to use Aggr(count(Distinct))

Anonymous — Tue, 14 Jul 2015 13:07:12 GMT

Hi, Manish,

You solution also works, thanks very much~

Re: problem to use Aggr(count(Distinct))

antoniotiman — Tue, 14 Jul 2015 13:31:38 GMT

Hi Zhihong,

If() can' work because Dim SubName has many values associated with Dim Name,

so, You need to aggregate

Regards,

Antonio