Skip to main content
Participating Frequently
Sep 6, 2023 7:28 AM UTC
Question

How to select unique rows from DataSubset based on one field

I have a rule rule expression which returns a datasubset in which the data has repeating rows. Since the data is coming from a view in mysql backend, I want to handle this in appian by getting only unique rows and remove all duplicate rows while still maintaining the paging. The data should be unique for 'stateroomRequestId'. I have tried using union but it does not work with datasubsets and only data

a!queryEntity(
  entity: cons!BUS_ENTITY_REQUEST_VIEW_DETAILS,
  fetchtotalcount: ri!fetchtotalcount,
  query: a!query(
    selection: if(
      rule!APN_isEmpty(ri!selection), 
      null,
      a!querySelection(
        columns: a!forEach(
          items: ri!selection,
          expression: a!queryColumn(
            field: fv!item
          )
        )
      )
    ),
    aggregation: ri!aggregation,
    logicalexpression: a!queryLogicalExpression(
      operator: "AND",
      logicalexpressions: ri!logicalexpressions,
      filters: {
        ri!filters
      },
      ignorefilterswithemptyvalues: true
    ),
    paginginfo: if(
      isnull(ri!paginginfo),
      a!pagingInfo(startIndex: 1, batchSize: 1),
      ri!paginginfo
    )
  )
)

3 replies

Participating Frequently
Sep 6, 2023 7:31 AM UTC

Here are the test inputs and paging for reference

stefanhelzle0001
Brainy
Sep 6, 2023 8:39 AM UTC

Repeated values in a view must be fixed in the view!

peter.lewis
Employee
Sep 6, 2023 1:12 PM UTC
I want to handle this in appian by getting only unique rows and remove all duplicate rows while still maintaining the paging

To be honest with you, I don't see how this is possible from a conceptual standpoint. If I have requested 10 rows with a certain sort, de-duplicating the data necessitates that the result won't have the same number of items, right?

Can you give more context on what you're trying to do with this data? Why does it need to stay as a datasubset?

And also to Stefan's point, this sounds like an issue with your database view. Can you change the view to support this data correctly?