I have records with duplicate unique IDs and I am trying to get the first record of a set of duplicate IDs and suppress the other record(s) that are associated with the same ID.

In the following sample UNIQUE_ID 557170000000210017 has two records associated with it.

Sample Records

EXP_REV,557170000001080359,2017-11-01,invoice,3010-05,3010-05 SaaS Revenue,Income,credit…

EXP_REV,557170000000210017,2017-11-02,invoice,3020-10,3020-10 SVC,Income,credit…
EXP_REV,557170000000210017,2017-11-02,invoice,3020-10,3020-10 SVC,Income,credit…

EXP_REV,557170000000248185,2017-11-30,invoice,3020-10,3020-10 SVC,Income,credit…

OneCloud can easily suppress the second instance of the UNIQUE ID with some Tabular Transformation BizApp Advanced Query Command. Follow these steps:

  • Use the OneCloud Tabular Transformation BizApp and select the "Advanced Query" Command.

  • Add the Table that needs to queried.

  • Use SQL to select only the first instance of the UNIQUE_ID column.

  • Enter the following SQL Query in the Query Field:

Select * from stage group by UNIQUE_ID having MIN(UNIQUE_ID) ORDER BY UNIQUE_ID
  • Select our input and output delimiters based on the file format you are using.

If you need additional support, please contact

Did this answer your question?