take the row with max date (Planned Done Date) or the date of the unfinished row

[Adam Petrie]

I'm pressed for time and reaching out for a hand.

Problem: I've started to encounter a situation where I'm getting multiple rows in my query and this is causing extra rows per Epic in my downstream reports.

My current sql: 

SELECT 
MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{4,}","g") AS 'Project',

FORMATWIKI("{cell:width=175px}","**Progress**: ",
    CASE WHEN (T1.'Status') IS NOT NULL THEN T1.'Status'
    ELSE "unkown" END,
    "\n**Story Link**: ",T1.'Key',
    "\n**Promised By**: ",
    CASE WHEN (T1.'Planned "Done" Date') IS NOT NULL THEN T1.'Planned "Done" Date'
    ELSE T1.'Due' END,"{cell}") AS 'Validate work'

FROM T1

What I'm thinking as a solution: Order the rows and use the row with max date

but there's a typo or a bug in my code here. Table Transformer gives me an error.

WITH CTE AS (
    SELECT 
        MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{4,}","g") AS 'Project',
        T1.'Status',
        T1.'Key',
        T1.'Planned "Done" Date',
        T1.'Due',
        ROW_NUMBER() OVER (PARTITION BY T1.'Epic Link' ORDER BY T1.'Planned "Done" Date' DESC) AS rn
    FROM 
        T1
)
SELECT 
    Project,
    FORMATWIKI(
        "{cell:width=175px}",
        "**Progress**: ", 
        CASE 
            WHEN Status IS NOT NULL THEN Status 
            ELSE "unknown" 
        END, 
        " **Story Link**: ", 
        Key, 
        " **Promised By**: ", 
        CASE 
            WHEN "Planned \"Done\" Date" IS NOT NULL THEN "Planned \"Done\" Date"
            ELSE Due 
        END,
        "{cell}"
    ) AS 'Validate work'
FROM 
    CTE
WHERE 
    rn = 1

... is Table Transformer sql able to handle the partitioning statement?

ROW_NUMBER() OVER (PARTITION BY T1.'Epic Link' ORDER BY T1.'Planned "Done" Date' DESC) AS rn

TIA

[Stiltsoft support · Accepted solution]

Hi @Adam Petrie ,

As you've mentioned the Table Transformer macro, we suppose that you use our Table Filter, Charts & Spreadsheets for Confluence app.

So, you may reach to our support portal directly if you have any questions regarding our macros (here we try to find the related questions manually).

Unfortunately, the PARTITION function is not supported. You can check the list of the supported functions in our documentation.

Maybe the simplest workaround is to use the internal Table Transformer macro for sorting and grouping (it seems that 'Planned "Done" Date' cells can be empty, so you may want to replace nulls by due dates in this query as well):

SELECT *
FROM (SELECT *
FROM T1 ORDER BY T1.'Planned "Done" Date' DESC)
GROUP BY 'Epic Link'

And leave all the formatting in the external Table Transformer:

SELECT
MATCH_REGEXP(MATCH_REGEXP('Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{4,}","g") AS 'Project',
FORMATWIKI(
"{cell:width=175px}",
"**Progress**: ",
CASE
WHEN 'Status' IS NOT NULL THEN 'Status'
ELSE "unknown"
END,
" **Story Link**: ",
'Key',
" **Promised By**: ",
CASE
WHEN 'Planned \"Done\" Date' IS NOT NULL THEN 'Planned \"Done\" Date'
ELSE 'Due'
END,
"{cell}"
) AS 'Validate work'
FROM T1

Seems that it should be working fine if we imagined your source table correctly.

And you still may refer to the support portal with screenshots and any other details: the portal is confidential, we'll be able to replicate exactly your table and come up with a more beautiful solution. 

[Adam Petrie]

Thank you