To get the output on the basis of duplicate values of a field

[ruma_barman]

Hi All,

I have a requirement to get the contract only those having more than one Asset

E.g  Input

Contract             Asset

11                         Asset 1
11                         Asset 2
22                         Asset 1
33                         360p
44                         Asset 1
44                        3037PS

Output:

Contract 

11

44

Thanks in advance,

Ruma

[StarinieriG]

Hi,

maybe you could try in this way

LOAD
Contract
Where Count>1
;
Load
Contract,
Count(Asset) as Count

From ...
Group By
Contract;

[ruma_barman]

Thanks for your reply.Can you suggest something on frontend using set analysis or something coz the datamodel is a bit complicated.

[StarinieriG · Accepted solution]

Try to use that as dimension =If(Aggr(Count(Asset),Contract)>1,Contract) and suppress null maybe

[ruma_barman]

Thanks for your solution . It worked. Can you tell if possible how to get the count of it .In this case it's 2.

[StarinieriG]

You could use Count(Asset) because in this way data are filtered by dimension

[ruma_barman]

If in a KPI ,I want to show?

[StarinieriG · Accepted solution]

Maybe this

=Count({<Contract={"=Count(Asset)>= 2"}>}Asset)

or this

=Sum(If(Aggr(Count(Asset),Contract)>1,Aggr(Count(Asset),Contract)))