topic Feltring a table by FirstSortedValue() in QlikView

Feltring a table by FirstSortedValue()

momofiore — Wed, 25 Nov 2020 16:16:04 GMT

is it possible to know just which ACTOR have made the last Action is it CAP or ESSILOR?.

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 Where Actor='ESSILOR' OR ActorOrganization ='CAP' ;

left join(temp1)

load Ticket,

FirstSortedValue(Actor,ActionTime) as lastActorOrganization

Resident temp1 Group By Ticket;

left join(FactTicketActivity)

load Ticket,lastActorOrganization

Resident temp1;

drop table temp1;

could you take a look please youssefbelloum‌

Re: Feltring a table by FirstSortedValue()

tresB — Fri, 20 Jul 2018 10:23:30 GMT

May be like:

....

left join

oad Ticket,

FirstSortedValue(Actor,ActionTime) as lastActorOrganization

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

Re: Feltring a table by FirstSortedValue()

YoussefBelloum — Fri, 20 Jul 2018 10:26:17 GMT

But like what is said to him on the last post, for the ticket ER:485891 he needs some exceptions, it should be CAP but he needs ESSILOR..

don't know how many exceptions you have

Re: Feltring a table by FirstSortedValue()

momofiore — Fri, 20 Jul 2018 14:26:31 GMT

I made a mistake here: FirstSortedValue(Actor,ActionTime) as lastActorOrganization it should be FirstSortedValue(Actor,-ActionTime) as lastActorOrganization

exeptions? some times there are ACTION which dont have an ACTOR. and this is a little problem. my objective is to get the last ACTOR is it ESSILOR or CAP. the other ACTORs are not important for me.

thank you guys

Re: Feltring a table by FirstSortedValue()

YoussefBelloum — Fri, 20 Jul 2018 14:54:24 GMT

Yes, I was using '-' sign on the previous post, go to the help of the FirstSortedValue() to get familiar with it's syntax.

Good luck