Handling Latest Records in widget (pivot/charts) in context of filters selected | Help and How-To

raghavendra · 2025-01-28T16:24:09.010000Z

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 rnFROM executions) subWHERE 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" } } }

MoreDiscussions

Handling Latest Records in widget (pivot/charts) in context of filters selected | Help and How-To | Sisense Community