Sorting count and removing duplicates

[#1]

Hi All,

Please find the below req and attached the same in sampledata.xls.

Req: 1.  If the unique ID(i.e 1001,1001) contains multiple SupervisorId(1000001,1000002) then we have to remove that and not considering at count level.
Eg. We have to remove the sr no 1,2 rows in the dimension level.
2.If the id (1004,1004) contains unique SupervisorId(500001,500001) then we have to take only one of the sr no whether 3 or 5 (i.e low or  high ) row data in the dimension and then we have to take that record as 1 in the count of expression level.

Thanks in adavance.

Teja Ravi

[Gysbert_Wassenaar]

See attached qvw

[tresesco]

For front-end solution PFA

[#4]

Thanks both of you.

Here my sr. no is coming from number format but it my original data the fields format is alphanumeric.So then when am applying the aggr(max([Sr no.]),ID)  at dimension level,its shwing blank records.

How we have to convert alphanemric format into number format.

[tresesco · Accepted solution]

Instead of Max() use MaxString().

[#6]

Thanks Tresesco.Its working.

Please find the below attached sample data.

To get the least or highest record should be 1 in the count level and the remaining records should be 0 at count level.

Thanks in adavnce.

Teja Ravi