Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
9 replies
Subscribers
7 subscribers
Views
5050 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
AI and Rules
Help with a Query Entity
Issam Dabaie
Certified Senior Developer
over 7 years ago
Hello,
I am trying to configure a query entity to return the following:
I have Four columns as follows in a view:
ID | FK | Decision ID | Date&Time
1 1 1 9/23
2 2 1 9/23
3 2 2 9/24
4 3 4 9/25
Where FK = 2, I would like to return the Decision ID with the maximum date, therefore ID = 3.
How can I configure this in a query entity?
Thanks!
OriginalPostID-237805
Discussion posts and replies are publicly visible
0
Issam Dabaie
Certified Senior Developer
over 7 years ago
My apologies, I meant I want to return all rows except the duplicate FK with the lower date.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sanjays0006
over 7 years ago
So you want to return rows with ID : 1,2,3 ?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Issam Dabaie
Certified Senior Developer
over 7 years ago
I'd like to return ID 1,3 4.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
ChristineH
Certified Lead Developer
over 7 years ago
I usually do this in the db with a view that has an inner select to only get the non dups, but one with highest value. Then I'll use a query entity against that.
Is that possible in your environment?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sanjays0006
over 7 years ago
It is better to implement this in the view level itself. I am not sure how to implement this in Appian but in the DB level, we can implement this.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Issam Dabaie
Certified Senior Developer
over 7 years ago
This is indeed possible, my aim was to keep it within Appian but if a view is the best solution then I will do that.
Thank you all for your input!
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
abhi.jana
Appian Employee
over 7 years ago
In Appian you can do something like:
local!data1: <All data where FK != 2>
local!data2: <first row of data where FK = 2 sorted by date descending>
local!result: union(local!data1, local!data2)
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
reginaldm377
over 7 years ago
I don't know if this will help you at all, but on the Appian side, I've done something like this before to return the maximum date.
local!variableName:todatetime(max(index(local!variable, "date", null)))
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Issam Dabaie
Certified Senior Developer
over 7 years ago
Thank you all for your input, I've decided to go with the view approach in the end.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel