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!

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