• State Suggested Answer
  • Replies 5 replies
  • Answers 1 answer
  • Subscribers 12 subscribers
  • Views 7857 views
  • Users 0 members are here
Related Discussions
Home » Discussions » User Interface

Is there a smarter way to pick unique records being fetched by a query expression?

Sam

Hi All,

My name is Sam and i am new to Appian development and hence might be asking a few rudimentary questions. For this one, i have a query expression that is returning me a year value as {2019, 2019,2019,2018,2018,2017}. As you see this is returning values from different rows of a database. Now what i would like to do is get the unique values from this list so my output should like "{2019,2018,2017}". After studying the built in functions the only function i was able to use to achieve what i need was union () where my code looked like union((rule!<rule>(year)),rule!<rule>(year))) , where i am doing a union between the same set of data. Somehow i feel this is not efficient. Is there any other built in function that woudl allow me to extract the unique values from my array list with duplicates.

Any pointers would be helpful.

Thanks again community.

 

Reg,

Sam

  Discussion posts and replies are publicly visible

  • 0
    You could also aggregate your data during the query.

    aggregation: a!queryAggregation(aggregationColumns: {

    a!queryAggregationColumn(field: "yourFieldName", isGrouping: true),
    })

    You'll have to test to see which is more efficient.
  • 0
    A Score Level 1
    Hi Sam,

    I would do a new expression rule to eliminate duplicates.

    The union is not a bad approach, however I would create a new expression rule that removes the duplicates. In general, you should try to minimize calls to the DB so supporting with an expression rule that has a "ri!" or using a load() variable would be better.

    Another way would be by doing loopings. In general, Appian does not work very well with arrays. For arrays I usually work with loops, as then you can work item by item and it is more transparent what you are doing.

    Probably this functions would be useful.

    wherecotains() docs.appian.com/.../fnc_array_wherecontains.html

    a!forEach() docs.appian.com/.../fnc_looping_a_foreach.html

    Anyways, an expression rule that eliminates duplicates is always useful.

    Good luck!
  • 0
    A Score Level 1
     in reply to ManuelHTG
    by the way, always good to look for some query recipes.

    From here Appian recommends a very easy approach, to use the group parameter. That would be the best way as you would only have one call to the DB and as you dont need to process it after

    Check "Get the Distinct Values of a Field"

    docs.appian.com/.../Query_Recipes.html


    a!queryEntity(
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
    aggregation: a!queryAggregation(
    aggregationColumns: {
    a!queryAggregationColumn(
    field: "department",
    isGrouping: true
    )
    }
    ),
    pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: -1,
    sort: a!sortInfo(
    field: "department",
    ascending: true
    )
    )
    )
    ).data.department
  • 0
    Certified Lead Developer
    For dead simplicity:

    local!originalData: a!queryEntity(...),
    local!condensedData: union(local!originalData, local!originalData)

    You grab all the data, you keep all of it should you need it, but you also get a smaller list of unique values to use where appropriate, and 1 call to the DB.

    This might be the preferred solution for a small number of rows, but if it gets big, you're going to see the grouping aggregation save your bacon. You're going to want less data in your server's RAM and less data coming from the database. But for smallish data this might be a little faster. You'll have to benchmark and see.

    uniqueValues() would be a very handy, dandy function for Appian to implement, if it didn't amount to self union under the hood.
  • 0
    Also to note, included with the Appian Common Objects application is: rule!APN_distinct(). This function utilizes union as well:

    union(ri!array, ri!array)
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.