Search code examples
qlikviewqliksense

Return an associated field based on an aggr() answer?

So I have this problem that we want to find out per shift which machine started working last. I.e. which machine is the last to register a load. So this

max(aggr(min({<ACTIVITY={'Loading'}>}[RECORD START TIME]),
       [CYCLE RECORD SHIFT],[CYCLE PRIMARY MACHINE]))

gives me the correct time but now I want to be able to return the machine name as well.

Here is a sample of data

load * inline [
ACTIVITY,CYCLE PRIMARY MACHINE,CYCLE RECORD SHIFT,CYCLE SHIFT START TIME,RECORD START TIME
Loading,DT90015,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 11:45:17
Loading,DT90015,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 12:02:14
Loading,DT90015,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 12:21:32
Loading,DT90023,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 08:12:48
Loading,DT90023,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 08:50:43
Loading,DT90023,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 09:17:27
Loading,DT90023,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 09:53:19
Loading,DT90023,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 11:51:52
Loading,DT90023,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 12:07:09
Loading,DT90023,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 12:29:23
Loading,DT90024,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 08:08:54
Loading,DT90024,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 08:24:51
Loading,DT90024,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 08:40:15
Loading,DT90024,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 09:02:07
Loading,DT90024,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 09:25:59
Loading,DT90024,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 09:57:36
Loading,DT90024,2016/02/25.0,25/02/2016 07:00:00,25/02/2016 12:37:09
];

Thanks Eldad for the suggestion but that returns this:

Per shift performance

I should probably have included the table I want to create in my original question

Solution

  • Sorry for the delay, So here is a working solution, Basically in your script you need to create another aggragated table like 

    LastMachine:
Load [CYCLE RECORD SHIFT], max([RECORD START TIME]) as max_ts Resident Data group by [CYCLE RECORD SHIFT];
left join
Load [CYCLE RECORD SHIFT],[RECORD START TIME] as max_ts, [CYCLE PRIMARY MACHINE] as Last_Machine Resident Data;

    and then you get

    enter image description here