Search code examples
spotfire

Displaying only multiple payments in Spotfire

hello everyone, I have a requirement where I am supposed to display the request_id and other details for just the multiple/duplicate payments in Spotfire based on bank name and bank account number.

The approach I took was to create a calculated column with rowid that has same bank name and bank account number. After that, filter the data for which the rowid is greater than 1. But with this approach, along with non-duplicate request_id, I also filter the row_id "1" of the duplicate payment. Below is my sample code and data:

calculated column for duplicate: Rank(RowId(),"asc",[Payee Bank Account Number],[Payee Bank Name])

Data Filter: [calculated column for duplicate]>1 Sample Data

with my current approach, you can see request_id "387" will be missing from the final report.

Solution

  • @Shane- Quick review of your approach: If you rank Bank A/C and Bank name columns, the duplicate as well as non-duplicate ones will be ranked. In that case, when you filter records based on the condition [calculated column for duplicate]>1, the request_id's which are ranked 1 will be ignored.

    Instead of RANK, you could use COUNT.
    As your goal is to find duplicates, COUNT will suit this scenario.

    Please try the below solution.

    If(Count([Bank A/C]) over ([Bank name],[Bank A/C])>1,[request_ID])

    Here is the final output of the table:

    enter image description here

    Hope this answers your question!