Cut out Dataset with a calculated dimension

[Applicable88]

Hello,

I have the following table: 

QueueNo.	ActionNo.	ActionName	Start	Finished
1111	5000	Start	08:00	08:05
1111	5001	Moving	08:05	08:10
1111	5002	Parking	08:10	08:12
1111	5003	Moving	08:12	08:29
1111	5004	Success	08:29	08:30
2222	5100	Start	09:00	09:00
2222	5101	Moving	09:01	09:10
2222	5102	Failed	09:10	09:10

I want a calculated dimension where I only see the first and last actionNo of a QueuNo. I tried different methods to cut out the sub-actions. but I either get complete null or nothing happens. 

Hope someone has an idea. 

Best. 

[Taoufiq_Zarra]

@Applicable88  if I understood correctly first and last correspond to Start Success or Failed, you can try below

in calculated dimension for ActionNo :

=aggr(Only({<ActionName={'Start','Success','Failed'}>}[ActionNo.]),[ActionNo.])

the output from table :

[Applicable88]

Hi  @Taoufiq_Zarra  thanks,

there is much more ActionNames....but with your aggregation it come close to what I want. 

Is there a possiblity using a aggregation towards the Min Startedtime and the MaxFinishedtime of the Same QueueNo?

Means : Return only the first and last action of a queue, but nothing in between.  Otherwise there are plenty of ActionNames I have to include or exclude.

I hope you have an idea. 

Thx! 

[Taoufiq_Zarra]

@Applicable88  can you give an example , and expected output ?

[Applicable88]

  I show you with before/after:

Before:

QueueNo.	ActionNo.	ActionName	Start	Finished
1111	5000	Start	08:00	08:05
1111	5001	Moving	08:05	08:10
1111	5002	Parking	08:10	08:12
1111	5003	Moving	08:12	08:29
1111	5004	Success	08:29	08:30
2222	5100	Start	09:00	09:00
2222	5101	Moving	09:01	09:10
2222	5102	Failed	09:10	09:10

After:

1111	5000	Start	08:00	08:05
1111	5004	Success	08:29	08:30
2222	5100	Start	09:00	09:00
2222	5102	Failed	09:10	09:10

So I mean to use the time to filter instead of the Actions.

[MayilVahanan · Accepted solution]

Hi @Applicable88 

Try like below

=Aggr(Only({<ActionNo. = {$(=Concat(Aggr(Min(ActionNo.),QueueNo.),',')),$(=Concat(Aggr(Max(ActionNo.),QueueNo.),','))}>}ActionNo.), ActionNo., QueueNo.)

But, you can create a flag in script for better performance

T2:
LOAD *, If(QueueNo. <> Peek(QueueNo.), 1, Peek(Row)+1) as Row INLINE [
QueueNo., ActionNo., ActionName, Start, Finished
1111, 5000, Start, 08:00, 08:05
1111, 5001, Moving, 08:05, 08:10
1111, 5002, Parking, 08:10, 08:12
1111, 5003, Moving, 08:12, 08:29
1111, 5004, Success, 08:29, 08:30
2222, 5100, Start, 09:00, 09:00
2222, 5101, Moving, 09:01, 09:10
2222, 5102, Failed, 09:10, 09:10
];

Temp:
LOAD QueueNo., Min(Row) as Row Resident T2 group by QueueNo.;
LOAD QueueNo., Max(Row) as Row Resident T2 group by QueueNo.;

Join(T2)
LOAD *, 1 as flag Resident Temp;

DROP Table Temp;

Front end: =Aggr(Only({<flag ={1}>}ActionNo.), ActionNo.)