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;
