Filter by dimension but does not remove all other Instances

[asudo23]

Hello,

Newbie here. I have the following dataset with a Unique Id which represents 1 event, at the end of the event it outputs a Type which i would like to filter on  but when i filter by Type it removes all the other instances besides the End instance. This causes my calculation for Duration Max(Timestamp) - Min(Timestamp) to crash cause when filtered by Type it removes the Min(Timestamp). Not sure what the best way to solve this is, really appreciate any help or direction, thank you. 

ID	Event	Type	Status	Timestamp
10	Start			3/23/2022 10:00
10	Transaction		Failed	3/23/2022 10:10
10	Transaction		Successful	3/23/2022 10:20
10	End	C		3/23/2022 10:30

[Vegar]

You can achieve this by using set analysis. 
Try an expression like this: 
interval(aggr(NODISTINCT
Max({<Type,ID=P({<Type=Type>}ID)>} Timestamp)
- Min({<Type,ID=P({<Type=Type>}ID)>} Timestamp)
,ID),'hh:mm'
)

Alternatively you adjust your data model and add associate Type to all rows with the same ID. Then you will just need max(Timestamp)-Min(Timestamp) to calculate what you want. 

See sample script below:

SET NullInterpret =''; //Interpets empty cells in inline as null()
SET TimestampFormat='MM/DD/YYYY hh:mm';

RAW:
LOAD ID, Event, Trim(Type) as Type, Status, Timestamp inline[
ID, Event, Type, Status, Timestamp
10, Start,  ,  , 3/23/2022 10:00
10, Transaction,  , Failed, 3/23/2022 10:10
10, Transaction,  , Successful, 3/23/2022 10:20
10, End, C,  , 3/23/2022 10:30
11, Start,  ,  , 4/23/2022 10:00
11, Transaction,  , Failed, 4/23/2022 10:10
11, Transaction,  , Successful, 4/24/2022 10:20
11, End, D,  , 4/24/2022 10:30
];
//Exit script here if you want sample to use with setanalysis solution

Transactions: LOAD ID, Event, Status, Timestamp RESIDENT RAW;
DimType: LOAD DISTINCT ID, Type RESIDENT RAW where not isnull(Type);
DROP TABLE RAW;

[asudo23]

Hi Vegar, 

thank you for the reply.

what does the "ID=P" mean in the set analysis?

Andrew