Hi,
I have a table with test cases (testcase_key, Type), cycles (cycle1, cycle2, ...), and executions (test_result, execution_timestamp).
Scenario: A test can be executed multiple times within one cycle, and the same test case can also be executed in subsequent cycles. I want to keep only the latest execution of each test case in a pivot table. The dashboard has a filter for cycles.
I can write custom SQL like this:
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY testcase_key ORDER BY execution_timestamp DESC) as rn
FROM executions
) sub
WHERE rn = 1;
The challenge with this approach is that it only shows the test case of the latest cycle. If a user selects any past cycle, I am not able to see the latest test case status and type for that cycle.
I have also tried using JAQL, but it doesn't work as expected. Here is the JAQL query I used:
{
"top": 1,
"by": {
"table": "executions",
"column": "actualenddate_ts",
"dim": "[executions.actualenddate_ts]",
"datatype": "numeric",
"agg": "max"
},
"context": {
"partition": [
{
"table": "executions",
"column": "testcase_key",
"dim": "[executions.testcase_key]",
"datatype": "text"
},
{
"table": "executions",
"column": "issue_key",
"dim": "[executions.issue_key]",
"datatype": "text"
}
],
"sort": {
"table": "executions",
"column": "actualenddate_ts",
"dim": "[executions.actualenddate_ts]",
"datatype": "numeric",
"order": "desc"
}
}
}
