Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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.