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: 
momofiore
Creator
Creator

feltring a table by time to find last action on a data

Hey guys

Here attached the data . each ticket has many Action and each Action has an actor and the ActionTime is the time when the Actor made the Action


How can i figure out  the last action made for each ticket and the actor of this action ? i mean basing on the field ActionTime.

and would like to store them in other in column LastAction and LastActor


for example for the ticket ER:485506

lastAction is: TR

lastActor is: FR_EUR



1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

Hi,

one solution to do this is using FirstSortedValue(), like attached below

View solution in original post

9 Replies
momofiore
Creator
Creator
Author

youssefbelloum‌ and stevedark‌  guys could you please take a look for this

YoussefBelloum
Champion
Champion

Hi,

one solution to do this is using FirstSortedValue(), like attached below

momofiore
Creator
Creator
Author

thanks youssefbelloum‌ . could you more explaine please i still newest in qlik. did you attached somethings?

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Mohammed,

You need to view the thread directly, rather than in your notifications to see attachments.  Just click the discussion topic to do this.

I would typically approach this in the load script, with a group by and a where exists, but FirstSortedValue could do the trick.

Steve

momofiore
Creator
Creator
Author

thanks youssefbelloum

momofiore
Creator
Creator
Author

stevedark‌ thanks so much

YoussefBelloum
Champion
Champion

You're welcome

momofiore
Creator
Creator
Author

youssefbelloum‌ is it possible to know just which ACTOR have made the last Action is it CAP or ESSILOR?. using the same function

for example :

for the ticket ER:485832 last Action is made by CAP

for the ticket ER:485891 last Action is made by ESSILOR  even if there is an Action after the one made by ESSILOR


I mean i want to just to take in consideration the two values CAP  or ESSILOR, which one have made the last Action


this is my script but it is not working for ER:1342684


temp1:

load Ticket,ActionTime,Actor

resident FactTicketActivity;

left join(temp1)

load Ticket,

FirstSortedValue(Actor,ActionTime)  as lastActorOrganization

Resident temp1 Where Actor='ESSILOR' OR ActorOrganization ='CAP' Group By Ticket;

left join(FactTicketActivity)

loadTicket,lastActorOrganization

Resident temp1;

drop table temp1;

i would be so thankfull youssefbelloum‌ if you take a look

@

YoussefBelloum
Champion
Champion

Reading this:

for the ticket ER:485832 last Action is made by CAP

for the ticket ER:485891 last Action is made by ESSILOR  even if there is an Action after the one made by ESSILOR


in the ticket: ER:485891 the last value depending on the ActionTime field is CAP and not ESSILOR..


is there some exceptions ??