if fieldname equals then subtract from previous instance

[#1]

Hi Experts

I hope you can help, I am loading in from a Notepad file and my data looks a bit like the below

Name	Stage	Date Tray	Status
Apple	Open	01/01/2012	RDY
Apple	Open	02/01/2012	CMP
Apple	Book	03/01/2012	CMP
Apple	Review	03/01/2012	RDY
Apple	Review	03/01/2012	RUN
Apple	Review	04/01/2012	CMP
Apple	Book	05/01/2012	CMP
Apple	Review	06/01/2013	RDY
Apple	Review	06/01/2013	RUN
Apple	Review	06/01/2013	CMP
Apple	Close	06/01/2013	RUN
Apple	Close	07/01/2013	CMP

Basically its a workflow where the apple is moving to different stages, what I need to do is calculate the stage times , but here's the clincher - the stages are non sequential ie it can move in any order and I only wish for it to calculate when the status is equal to CMP.

So in the load statement something like

if(Status = CMP, date(date(Date Tray)),null()) as [Start Date].

Then

For each [Start Date] - next [Start Date]  as Stage time.

Any advice would really be appreciated

Thanks

A

[#2]

Name	Stage	Date	Status	Tray Time
Apple	Open	01/01/2012	RDY
Apple	Open	02/01/2012	CMP	1
Apple	Book	03/01/2012	CMP	1
Apple	Review	03/01/2012	RDY
Apple	Review	03/01/2012	RUN
Apple	Review	04/01/2012	CMP	1
Apple	Book	05/01/2012	CMP	367
Apple	Review	06/01/2013	RDY
Apple	Review	06/01/2013	RUN
Apple	Review	06/01/2013	CMP	1
Apple	Close	06/01/2013	RUN
Apple	Close	07/01/2013	CMP

So results would look like the above

[#3 · Accepted solution]

Hi Anne,

PFA,

Hope this will help

But i dont know how 367 comes??

[#4]

Hi Adeel

Thanks so much you have helped me understand the logic,

With a slight tweak its perfect for me

Kind regards

A

[#5]

Could you please share the change..??

It will help us in the future.

[#6]

Hi Adeel

it was as I didnt specify that there would be other names Name, so I just to sorted by name and I defined CMP date as part of the first table, very small  See full code below (Changes in bold) :

Thanks again

A

Wrkflow:
LOAD Process_Code,
     Application_id as Application_Id,
     Workitem_Id,
     Activity_Code,
     Timestamp(Txn_Date)as Txn_Date,
     State_Code,
     Assigned_User_Id,
     Assigned_Dept_Id,
     Actioned_User_Id,
     applymap('Trays',Activity_Code,null()) as Tray,
     applymap('Trays',Activity_Code,null())& State_Code as Link,
     if(State_Code='CMP',Timestamp(Txn_Date),null()) as [CMP_Date]

    
FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Wrkflow2:
Load *,1
Resident Wrkflow
Order by Application_Id,CMP_Date,Tray,State_Code;

DROP Table Wrkflow;

Wrkflow3:
Load *,
CMP_Date-if(State_Code='CMP' ,(Previous(CMP_Date)),'') as diff,
if(State_Code='CMP' ,(Previous(CMP_Date)),'') as pre
Resident Wrkflow2;

DROP Table Wrkflow2;