Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ebrahimaljefri
New Contributor III

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
Valued Contributor II

Re: Last function

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
Contributor III

Re: Last function

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;

kellymcgrady
Contributor

Re: Last function

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.

Community Browser