List only the duplicate values in the Pivot

[Anonymous]

Hi Team,

Need your help in displaying only the duplicate values from the data.

Kindly refer the attachment.S_No is the key.

I would like to list the highlighted(all 3 headers) values(only) in the pivot.

It will be great if this can be done in the set analysis instead of modifying the datamodel script

Thanks & Regards

Jeba

[Gysbert_Wassenaar]

See attached example

[sunny_talwar]

Try this script:

Table:

LOAD S_No,

    Status,

    Date

FROM

[..\..\Downloads\Test (2).xlsx]

(ooxml, embedded labels, table is Sheet1);

Join (Table)

LOAD S_No,

  Status,

  Count(S_No & Status) as Count

Resident Table

Group By S_No, Status;

And then create a chart like this using the following expression:

=Only({<Count = {">1"}>}Date)

I hope this will help.

Best,

Sunny

[pokassov]

if(aggr(Count(TOTAL <S_No, Status> DISTINCT Date),S_No, Status)>1,1)

[Anonymous]

Hi Gysbert,

Thanks for your quick reply.

This works perfectly for this scenario,however it doesn't work when I have some other scenario with the same concept.

Will the same scenario not work when we have the S_No as text format?Updated file attached for your kind reference.

Kindly help

Thanks & Regards
Jeba

[Anonymous]

Hi pokassov,

Though it displays the duplicate values,but it displays only one row from the duplicate

Regards

Jeba

[sunny_talwar]

You want to see them twice or seeing them once will be enough??? Below it is showing that those two S_No have more than 2 entries in the database.

HTH

Best,

Sunny

[Anonymous]

Hi,

I want to see twice/thrice or based on the actual duplicate entries

Is that possible?

Thanks & Regards

Jeba

[Anonymous]

also one more point.

Can this be done only based on the set analysis without disturbing the datamodel?

[Gysbert_Wassenaar · Accepted solution]

See attached example.