topic Re: Select only newest row from table in App Development

Select only newest row from table

Giddyap — Wed, 03 Nov 2021 12:25:44 GMT

Hi, everybody!

I hope you can help a Qlik rookie:

In this table, a person can have several rows. (Row 1 and 2 is the same person, as no. 9 and 11.)

When a person has several rows, I only want to show the last/most recent. (S_INSERTED_TS is the time stamp.)

The screenshot example is sorted descending on S_INSERTED_DATE. In this example, row 2 and 11 need to be excluded.

How do I write this code in the Data load editor?

I'm using FirstSortedValue to find the newest instance in the whole table*, but I need som kind of loop to find the last record for each person (which is identified with FNR).

So, for each FNR, I need to show only the record with the last value in S_INSERTED_DATE.

* Load FirstSortedValue([UNGDOMOTJOURNAL.FNR], -UNGDOMOTJOURNAL.S_INSERTED_TS, 1) as Siste_fnr

The whole table's got 37 000 rows.

Thank you.

Re: Select only newest row from table

stevejoyce — Wed, 03 Nov 2021 12:48:30 GMT

left join (UNGDOMOTJOURNAL)

load

1 as max_fnr_date,

max(UNGDOMOTJOURNAL.S_INSERTED_TS) as UNGDOMOTJOURNAL.S_INSERTED_TS,

UNGDOMOTJOURNAL.FNR

resident UNGDOMOTJOURNAL

group by UNGDOMOTJOURNAL.FNR;

then your front end can limit to max_fnr_date = {1}. or if you want to remove older data you can use inner join.

Re: Select only newest row from table

Giddyap — Mon, 15 Nov 2021 11:38:47 GMT

Thanks a lot! It worked. I'm not sure exactly how, so I hope you can enlighten me 🙂

Current code:

OTJ:
LOAD
    UNGDOMOTJOURNAL.FNR,
    UNGDOMOTJOURNAL.OTSTATUS_KODE,
    UNGDOMOTJOURNAL.OTSTATUS_SKOLEAR,
    UNGDOMOTJOURNAL.OTSTATUS_SKOLEAR&'|'&UNGDOMOTJOURNAL.FNR AS %nokkel,
    UNGDOMOTJOURNAL.S_INSERTED_TS
FROM [lib://VIGOS_Extract_03/VIGOS_Prefix/VIGOS_03_UNGDOMOTJOURNAL.qvd](qvd)
;

ELEVKURS:
LOAD
      ELEVKURS.SKOLEAR,
      ELEVKURS.FNR,
ELEVKURS.SKOLEAR&'|'&ELEVKURS.FNR as %nokkel,
      ELEVKURS.KURSKODE,
      ELEVKURS.SLUTTDAT,
      IF (IsNull(ELEVKURS.SLUTTDAT), 'Aktivt elevkurs', 'Avbrutt elevkurs') AS Elevkursstatus
FROM [lib://VIGOS_Extract_03/VIGOS_Prefix/VIGOS_03_ELEVKURS.qvd](qvd)
;

Test:
LEFT JOIN (OTJ)
LOAD
1 AS max_fnr_date,
MAX(date(UNGDOMOTJOURNAL.S_INSERTED_TS)) AS Siste_dato,
UNGDOMOTJOURNAL.FNR
RESIDENT OTJ
GROUP BY UNGDOMOTJOURNAL.FNR;

Q1)
I'm used to define variables the other way, e.g.

Table.variable as Variable_name
or
Table.variable as %key_name

In your code, the variable 1 is defined initially.
What is the number 1? I found that I can call it anything I want, e.g. 'Bob' og 321.

Q2)

When a person's status change, the table UNGDOMOTJOURNAL gets a new row with a new timestamp S_INSERTED_TS. In my table, S_INSERTED_TS shows the latest date for the given status (OTUK).

However, the latest date (Siste dato) shows the last date regardless of status.

I only want to show the person's last status (primarily OTUK) if this status is the latest one. All the cases in the screendump now have changed their status (from OTUK to something else). Therefore, they're not really relevant anymore. They're only relevant if the status OTUK is also the latest post on that person.

How can I code this?

Re: Select only newest row from table

Giddyap — Thu, 25 Nov 2021 11:13:51 GMT

I'm sorry to bother you again, but do anyone have a solution for my problem?

I only want to show those records where the two dates in column Siste_dato and column UNGDOMOTJOURNAL.S_INSERTE... match.

In my new enclosed example, row 5, 6, 8, 9, 10, 11, 12 and 13 must be excluded from the result, as Siste_dato is newer than UNGDOMOTJOURNAL.S_INSERTE... Only rows 1, 2, 3, 4 and 7 is to be shown.

Re: Select only newest row from table

PrashantSangle — Thu, 25 Nov 2021 11:29:42 GMT

can you explain with sample data. It looks very simple but with data we can provide the exact solution.

Re: Select only newest row from table

Giddyap — Thu, 02 Dec 2021 13:28:56 GMT

Hi.

Can you please elaborate? I've pasted the code and enclosed screen shot. I will of course give more info if needed.