Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.)
@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 :
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!
@Applicable88 can you give an example , and expected output ?
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.
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.)