topic Re: Re: if fieldname equals then subtract from previous instance in QlikView

if fieldname equals then subtract from previous instance

Thu, 19 Dec 2013 11:36:43 GMT

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

Re: if fieldname equals then subtract from previous instance

Thu, 19 Dec 2013 11:39:30 GMT

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

Re: Re: if fieldname equals then subtract from previous instance

Thu, 19 Dec 2013 12:09:07 GMT

Hi Anne,

PFA,

Hope this will help

But i dont know how 367 comes??

Re: Re: if fieldname equals then subtract from previous instance

Thu, 19 Dec 2013 12:41:14 GMT

Hi Adeel

Thanks so much you have helped me understand the logic,

With a slight tweak its perfect for me

Kind regards

A

Re: Re: if fieldname equals then subtract from previous instance

Thu, 19 Dec 2013 12:46:07 GMT

Could you please share the change..??

It will help us in the future.

Re: Re: if fieldname equals then subtract from previous instance

Thu, 19 Dec 2013 13:22:41 GMT

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

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;