Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
RoyBatty
Contributor III
Contributor III

Keep only the most recent (by id)

Hi,

I have the following data (table):

[activities]:
Load * Inline
    [
       id, task_id, activity,
       1, 1, "run"
       2, 1, "sleep"
       3, 2, "eat"
       4, 1, "eat"
       5, 2, "sleep",
       6, 3, "run"
    ]
;

AGsZ7Jg

I want to keep only the most recent records for each task_id (and the ones with the largest id are the most recent). So the result should look like this:

PzjhZJE

Can you help us with this? We tried the following solution:

[activities]:
Load * Inline
    [
       id, task_id, activity,
       1, 1, "run"
       2, 1, "sleep"
       3, 2, "eat"
       4, 1, "eat"
       5, 2, "sleep",
       6, 3, "run"
    ]
;

[temp1]:
Load
    task_id,
    Max(id) as max_id
Resident activities
Group By task_id;

Left Join (temp1)
Load
    id,
    task_id,
    activity
Resident activities;


Drop Table activities;

Rename Table temp1 to activities;

but it didn't work:

FlCmkC7

 

Labels (1)
1 Solution

Accepted Solutions
BrunPierre
Master
Master

[activities]:
Load * Inline [
        id, task_id, activity, 
        1, 1, "run"
        2, 1, "sleep"
        3, 2, "eat"
        4, 1, "eat"
        5, 2, "sleep",
        6, 3, "run" ];
        
Inner Join
LOAD
Max(id) as id 
Resident activities
Group By task_id;

View solution in original post

9 Replies
vincent_ardiet_
Specialist
Specialist

Just change the field name here:

[temp1]:
Load
    task_id,
    Max(id) as id
Resident activities
Group By task_id;
BrunPierre
Master
Master

[activities]:
Load * Inline [
        id, task_id, activity, 
        1, 1, "run"
        2, 1, "sleep"
        3, 2, "eat"
        4, 1, "eat"
        5, 2, "sleep",
        6, 3, "run" ];
        
Inner Join
LOAD
Max(id) as id 
Resident activities
Group By task_id;
PrashantSangle

You can achieve the same with where exists try below

 

[temp1]:

Load

    task_id,

    Max(id) as max_id

Resident activities

Group By task_id;

 

Noconcatenate

Final:

Load

    id,

    task_id,

    activity

Resident activities

Where exists (id,max_id)

 

 

Drop Table activities, temp1;

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
RoyBatty
Contributor III
Contributor III
Author

@vincent_ardiet_ It works, but I don't understand why: why it worked when I changed it to be "id"? Because, even when it's not "id" but "max_id", isn't it supposed to left-join by task_id (and only keep those from temp1?)

 

Working Solution:

[activities]:
Load * Inline
[
id, task_id, activity,
1, 1, "run"
2, 1, "sleep"
3, 2, "eat"
4, 1, "eat"
5, 2, "sleep",
6, 3, "run"
]
;

[temp1]:
Load
task_id,
Max(id) as id
Resident activities
Group By task_id;

Left Join (temp1)
Load
id,
task_id,
activity
Resident activities;


Drop Table activities;

Rename Table temp1 to activities;

 

 

vincent_ardiet_
Specialist
Specialist

Because your task_id are not unique values.
[temp1] contents:
max_id, task_id
4, 1, "eat"
5, 2, "sleep",
6, 3, "run"

Then you join with [activities], the join is using only common fields, here there is only [task_id].
QS is keeping in [activities] all rows with a task_id among 1, 2 and 3. So everything.

RoyBatty
Contributor III
Contributor III
Author

It works and even more elegant (as it has less lines of code).

RoyBatty
Contributor III
Contributor III
Author

When I tried to run, I got the following error: Field 'max_id' not found

vincent_ardiet_
Specialist
Specialist

Yes because it should be 
Where exists (max_id,id)

RoyBatty
Contributor III
Contributor III
Author

Oh yes! Thank you @vincent_ardiet_