Displaying multiple Columns and aggregating data

Hi Jesusa,

To achieve this, we need to use two query entity. One QE will return maximum value of time for type. Second QE will return the result for Text based on Time and Type filters.

Please find out the code:

load(
local!maxTimeforType: a!queryEntity(
entity: cons!PG_ENTITY_DATA,
query: a!query(
aggregation: a!queryAggregation(
aggregationColumns: {
a!queryAggregationColumn(
field: "type",
isGrouping: true()
),
a!queryAggregationColumn(
field: "time",
aggregationFunction: "MAX"
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: - 1
)
)
).data,
local!typeDetails: a!queryEntity(
entity: cons!PG_ENTITY_DATA,
query: a!query(
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "type",
operator: "in",
value: tointeger(
local!maxTimeforType.type
)
),
a!queryFilter(
field: "time",
operator: "in",
value: todate(
local!maxTimeforType.time
)
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: - 1
)
)
).data,
local!typeDetails
)

Output would be this: 

• Dictionary
  • type 1
  • text "CC"
  • time 1/4/2020
  • id 3
• Dictionary
  • type 2
  • text "AA"
  • time 3/5/2020
  • id 1

Hi Jesusa,

To achieve this, we need to use two query entity. One QE will return maximum value of time for type. Second QE will return the result for Text based on Time and Type filters.

Please find out the code:

load(
local!maxTimeforType: a!queryEntity(
entity: cons!PG_ENTITY_DATA,
query: a!query(
aggregation: a!queryAggregation(
aggregationColumns: {
a!queryAggregationColumn(
field: "type",
isGrouping: true()
),
a!queryAggregationColumn(
field: "time",
aggregationFunction: "MAX"
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: - 1
)
)
).data,
local!typeDetails: a!queryEntity(
entity: cons!PG_ENTITY_DATA,
query: a!query(
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "type",
operator: "in",
value: tointeger(
local!maxTimeforType.type
)
),
a!queryFilter(
field: "time",
operator: "in",
value: todate(
local!maxTimeforType.time
)
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: - 1
)
)
).data,
local!typeDetails
)

Output would be this: 

• Dictionary
  • type 1
  • text "CC"
  • time 1/4/2020
  • id 3
• Dictionary
  • type 2
  • text "AA"
  • time 3/5/2020
  • id 1

Thanks for your answer. I have achieved what I wanted by creating a view in Oracle. By that way I only need to run one simple query in Appian and I think the peformance will be good.