Help Desk

Submit a ticket My Tickets
Welcome
Login

Can the Advanced Query Command suppress records with duplicate unique IDs?

Question
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 .

      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…

Answer
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 support@onecloud.io.

Login to post a comment