Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Last function

hi

i am using qlik sense desktop and i want to take last record for each employee of a created field which it is datatype is  Date and time

When i use below function it gives me wrong expression

LastChannel:

Right Join

LOAD LastValue(Created),

    "Created By",

    "Created By Name",

    Channel;

SQL SELECT Created,

    "Created By",

    "Created By Name",

    Channel

FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

regards

3 Replies
martinpohl
Partner - Master
Partner - Master

Aggregation function (like lastvalue) requires a group by statement

LastChannel:

Right Join

LOAD LastValue(Created),

    "Created By",

    "Created By Name",

    Channel

Group by "Created By", "Created By Name", Channel

;

SQL SELECT Created,

    "Created By",

    "Created By Name",

    Channel

FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

Maybe Creaters are different so you will get the last value for all creaters.

So load in two steps:

TempSql:

SQL select as below;

left join

load

     lastvalue(Created) as LastCreation,

Channel

Group by Channel;

resident Temp;

Data:

load

"Created By",

"Created By Name",

Channel,

LastCreation as Creation

resident Temp

where Creation = LastCreation;

drop table Temp;

Regards

jaumecf23
Creator III
Creator III

I think that you need to use a GROUP BY here. Something like this:

LastChannel:

Right Join

LOAD LastValue(Created),

    "Created By",

    "Created By Name",

    Channel

Group By "Created By",    "Created By Name",    Channel;

SQL SELECT Created,

    "Created By",

    "Created By Name",

    Channel

FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

Anonymous
Not applicable
Author

LastChannel:

LOAD First 1

    "Created By",

    "Created By Name",

    Channel;

FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

Order by desc;

Flip the order of the list and then take the first row which will always get you the last entry in the column even when you update.