problem to use Aggr(count(Distinct))

[Anonymous]

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

[MK_QSL]

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

[Anonymous]

Hi, Manish,

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

Zhihong

[MK_QSL]

What result you are expecting? Can you provide this ?

[antoniotiman · Accepted solution]

Hi,

see attachment

Regards,

Antonio

[Anonymous]

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

[jonathandienst]

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)

or

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

[Anonymous]

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

[MK_QSL]

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

[Anonymous]

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?