I have a complex case that I need help with that will probably end up in a store

I have a complex case that I need help with that will probably end up in a stored procedure of some sort.

Here's an example: From your query entity, you retrieve 100 rows of data, many of them all containing the same information, except for 1 column, for the following 4 columns:Application #, Part Time Students, Full Time Students, School Number

Your data looks like this initially:
(The count matches for each set = 7)

Application #: {115111,115112,115113,115114,115116,115117,115118}
Part Time Students: {1000,2000,1000,2000,1000,1000,2000}
Full Time Students: {500, 500,1000,500,1000,1000,1000}
School #: {1,2,1,2,1,2,1}

From here, you want to retrieve only distinct values from each set (this may even include nulls at any given point).
So you want to truncate your data to this since there are many duplicates:

Application #: {115111,115112,115113,115114,115116,115117,115118}
Part Time Students: {1000,2000}
Full T...

OriginalPostID-193992

OriginalPostID-193992

  Discussion posts and replies are publicly visible

  • ...ime Students: {500,1000}
    School #: {1,2}

    At this point, you want to match up the application # to their correct values from the other columns to display accurate data in a SAIL interface.

    The final case, that I have implemented currently on the SAIL interface side, is if the 1st index of values (from the 1st 3 columns) are the same as any other index from the data retrieved, then show only that index, otherwise, show all data (which is where matching the correct values comes into play)

    Any tips/helps/guidance on developing this would be greatly appreciated.
  • Can you please explain your use case a bit more? Is there a particular reason for taking this approach?
  • So when a hyperlink is clicked while previewing data for an app, I have to retrieve the columns I specified in my example into a grid but it shouldn't contain duplicates (because in this particular scenario, it's not necessary.) Should the data I'm querying contain the EXACT same data, then I only need to show only 1 of those rows (since they're the same).

    Otherwise, I should show all data where it differs (again, duplicates are unnecessary because it would just be a large grid of data showing the same result).

    My main issue is if there are a few/multiple/several columns of data that differ (but have some similarities in 1 or 2 columns), I want to make sure the info is aligning accurately instead of just doing an apply & merge of some sort that iterates through the values & does every combination possible because that would be, in some cases, incorrect when this is shown to a user which is why I'm taking this approach.
  • So, it sounds to me like you're wanting to display a grid, and you don't want any of the rows in the grid to be an exact duplicate of another row in the grid (where a different application number does not count as making a row unique). Is that correct?

    If so, for a small number of columns, as is the case in your example, one option might be to add a column in the database that is a combination of the three columns you wish to be unique. So, for your example, it might contain values such as "1-1000-1000". You could then use a!queryEntity() to retrieve the data from the database and aggregation to filter out non-unique results (see forum.appian.com/.../Query_Recipes.html ). This would be particularly easy to implement if you don't actually need to display the application #. The end result would be that you would only retrieve unique combinations of data to display in your grid.

    Would this approach work for you?
  • I believe this may work for me. I've been doing some playing around with the aggregate functions to see if any of the results would be something that could show the critical information more efficiently. Thanks Eliot!
  • I believe this may work for me. I've been doing some playing around with the aggregate functions to see if any of the results would be something that could show the critical information more efficiently. Thanks Eliot!

  • 0
    Certified Senior Developer
    in reply to sadiad0001

    But this post is from 6! years ago.
    A lot have changed since then within appian. often fundamentally.
    What do you try to archive?