Hi All,
I am using the export datastore entity to excel smart service to export records from database. But it gives duplicate records. I am using the filter condition as given below in data input section of smart service:
= a!queryLogicalExpression( operator: "AND", filters: { if(isnull(pv!study), {}, a!queryFilter( field: "study", operator: "in", value: pv!study ) ), if(isnull(pv!category), {}, a!queryFilter( field: "rootCauseCategory", operator: "in", value: pv!category) ), if(isnull(pv!siteDepotLoc), {}, a!queryFilter( field: "siteDepotLoc", operator: "in", value:pv!siteDepotLoc ) ), if(isnull(pv!excursionType), {}, a!queryFilter( field: "excursionType", operator: "in", value: pv!excursionType ) ), a!queryFilter( field: "dateReported", operator: "between", value: { pv!dateReportedFrom, pv!dateReportedTo }, applywhen: not( or( rule!APN_isEmpty(pv!dateReportedFrom), rule!APN_isEmpty(pv!dateReportedTo) ) )), if(isnull(pv!otherRootCause), {}, a!queryFilter( field: "otherRootCause", operator: "in", value: rule!APN_distinct(pv!otherRootCause) ) )})
Can anyone please suggest ho to remove the duplicate records.
Thank you
Discussion posts and replies are publicly visible
Can you clarify what you mean when you say "it gives duplicate records"? Are you saying there aren't duplicated records in the DB, but the result of the export DSE operation produces an excel WITH duplicated records?
If it's actually your data source that has duplicated records, but you don't want them exported to your Excel, perhaps the easiest way might be to write a View that shapes the data (indlucing de-duplication) per the requirements for your Excel, and then use that for your export instead of the original table.
I would typically utilize the view method as described by Mike, however to note you can also apply a!queryAggregation() within the aggregation parameter of the Export DSE to Excel node (or function), adding isGrouping: true for all of your columns will eliminate duplicates.
a!queryAggregation( aggregationColumns: { a!queryAggregationColumn( field: "requestNumber", isGrouping: true ), a!queryAggregationColumn( field: "submitterId", isGrouping: true ) } )