Show latest record, except...

Giddyap — Tue, 19 Apr 2022 11:15:58 GMT

Hi there

I need some help to get my app 100 %.

I have 3 joined tables, UNGDOMOTJOURNAL, ELEV2 and OTENHET. See screen dump no. 2

In the front end, I have a pivot table showing number (fnr), status and name (navn). See screen dump no. 3 and 4.

I'm extracting only the latest status: MAX(date(UNGDOMOTJOURNAL.S_INSERTED_TS)) AS UNGDOMOTJOURNAL.S_INSERTED_TS
However, as you can see from screen dump no. 1, not all rows have a status. This messes up my stats, as I need to show the last status regardless of rows without such.

When there's no status (OTSTATUS_KODE) on the latest row, I need to show the last row that has a status. For the year 20212022 this will be line no. 3 (OTUK).

Any suggestions?

Here's the code:

OT:
LOAD
[UNGDOMOTJOURNAL.FNR] as fnr,
[UNGDOMOTJOURNAL.FNR] as %fnr_key,
                [UNGDOMOTJOURNAL.OTSTATUS_KODE] as otstatus,
    [UNGDOMOTJOURNAL.TEKST] as ottekst,
    [UNGDOMOTJOURNAL.OTSTATUS_KODE]&[UNGDOMOTJOURNAL.TEKST] as kodeogtekst,
                [UNGDOMOTJOURNAL.OTSTATUS_SKOLEAR],
    UNGDOMOTJOURNAL.S_INSERTED_TS
FROM [lib://VIGOS_Extract_03/VIGOS_Prefix/VIGOS_03_UNGDOMOTJOURNAL.qvd](qvd)
WHERE [UNGDOMOTJOURNAL.OTSTATUS_SKOLEAR] = 20212022;

ELEV:
LEFT JOIN (OT)
LOAD
    ELEV2.FNR,
    ELEV2.FNR as %fnr_key,
    ELEV2.OTENHET as %otenhet_key
FROM [lib://VIGOS_Extract_03/VIGOS_Prefix/VIGOS_03_ELEV2.qvd]
(qvd);

OTENHET:
LEFT JOIN (OT)
LOAD
    OTENHET.AKTIV,
    OTENHET.NAVN,
    OTENHET.OTENHET,
    OTENHET.OTENHET as %otenhet_key,
    OTENHET.S_OBJECTID,
    OTENHET.S_STATUS,
    OTENHET.FYLKESNR
FROM [lib://VIGOS_Extract_03/VIGOS_Prefix/VIGOS_03_OTENHET.qvd](qvd)
WHERE [OTENHET.FYLKESNR] = 03 AND [OTENHET.AKTIV] = 'J';
//WHERE [OTENHET.FYLKESNR] = 03;

Test:
LEFT JOIN (OT)
LOAD
   'Kun_siste_dato' AS Max_fnr_date,
   MAX(date(UNGDOMOTJOURNAL.S_INSERTED_TS)) AS UNGDOMOTJOURNAL.S_INSERTED_TS,
   fnr
RESIDENT OT
GROUP BY fnr;

topic Show latest record, except... in App Development

Show latest record, except...