Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, Manish,
You solution also works, thanks very much~
Hi Zhihong,
If() can' work because Dim SubName has many values associated with Dim Name,
so, You need to aggregate
Regards,
Antonio