create a new field in script using last but 1 status

[hopkinsc]

Hi all,

i have a field named Order_Status_Name which holds the status of an order. an order would have multiple status and the majority of them would have a final status of 'Removed From Screens'.

I want to create a field that would have the value BEFORE the removed from screens value.

i.e.

Order ID	TimeStamp	Status
1001	01/09/2014 10:41	Generated
1001	01/09/2014 11:58	Job Started
1001	01/09/2014 14:23	Job Complete
1001	01/09/2014 23:54	Removed From Screens
12411	02/09/2014 06:12	Generated
12411	02/09/2014 09:33	Job Started
12411	02/09/2014 09:39	Specialist Required
12411	02/09/2014 23:54	Removed From Screens
12721	02/09/2014 15:21	Generated
12721	03/09/2014 09:52	Cancelled
12721	03/09/2014 23:54	Removed From Screens
12935	03/09/2014 11:49	Generated
12935	03/09/2014 11:49	Job Started
12935	03/09/2014 16:05	Job Complete

so the new field values would be:

1001 = Job Complete

12411 = Specialist Required

12721 = Cancelled

12935 = Job Complete.

I basically never want to see the status 'Removed from Screens'

Can anyone help me put that into the script please?

[sushil353]

Hi,

Try below code:

LOAD *,if(Status='Removed From Screens',previous(Status),Status) as newfield Inline

[

OrderID, TimeStamp, Status

1001, 01/09/2014 10:41, Generated

1001 ,01/09/2014 11:58,Job Started

1001 ,01/09/2014 14:23, Job Complete

1001 ,01/09/2014 23:54, Removed From Screens

12411, 02/09/2014 06:12, Generated

12411, 02/09/2014 09:33, Job Started

12411, 02/09/2014 09:39, Specialist Required

12411, 02/09/2014 23:54, Removed From Screens

12721, 02/09/2014 15:21, Generated

12721, 03/09/2014 09:52, Cancelled

12721, 03/09/2014 23:54, Removed From Screens

12935, 03/09/2014 11:49, Generated

12935, 03/09/2014 11:49, Job Started

12935, 03/09/2014 16:05, Job Complete

];

HTH

Sushil

[MK_QSL]

Temp:

Load

  [Order ID],

  Timestamp#(TimeStamp,'DD/MM/YYYY hh:mm') as TimeStamp,

  Status

Inline

[

  Order ID, TimeStamp, Status

  1001, 01/09/2014 10:41, Generated

  1001, 01/09/2014 11:58, Job Started

  1001, 01/09/2014 14:23, Job Complete

  1001, 01/09/2014 23:54, Removed From Screens

  12411, 02/09/2014 06:12, Generated

  12411, 02/09/2014 09:33, Job Started

  12411, 02/09/2014 09:39, Specialist Required

  12411, 02/09/2014 23:54, Removed From Screens

  12721, 02/09/2014 15:21, Generated

  12721, 03/09/2014 09:52, Cancelled

  12721, 03/09/2014 23:54, Removed From Screens

  12935, 03/09/2014 11:49, Generated

  12935, 03/09/2014 11:49, Job Started

  12935, 03/09/2014 16:05, Job Complete

];

NoConcatenate

Final:

Load *, If(WildMatch(Status,'*Removed From Screens*'), Peek(Status),Status) as RequiredStatus

Resident Temp

Order By [Order ID], TimeStamp;

Drop Table Temp;

[MK_QSL]

If you want only 4 rows at the end of the script use below

Temp:

Load

  [Order ID],

  Timestamp#(TimeStamp,'DD/MM/YYYY hh:mm') as TimeStamp,

  Status

Inline

[

  Order ID, TimeStamp, Status

  1001, 01/09/2014 10:41, Generated

  1001, 01/09/2014 11:58, Job Started

  1001, 01/09/2014 14:23, Job Complete

  1001, 01/09/2014 23:54, Removed From Screens

  12411, 02/09/2014 06:12, Generated

  12411, 02/09/2014 09:33, Job Started

  12411, 02/09/2014 09:39, Specialist Required

  12411, 02/09/2014 23:54, Removed From Screens

  12721, 02/09/2014 15:21, Generated

  12721, 03/09/2014 09:52, Cancelled

  12721, 03/09/2014 23:54, Removed From Screens

  12935, 03/09/2014 11:49, Generated

  12935, 03/09/2014 11:49, Job Started

  12935, 03/09/2014 16:05, Job Complete

];

NoConcatenate

Final:

Load *, If(WildMatch(Status,'*Removed From Screens*'), Peek(Status),Status) as RequiredStatus

Resident Temp

Order By [Order ID], TimeStamp;

Drop Table Temp;

Left Join (Final)

Load

  [Order ID],

  TimeStamp(Max(TimeStamp),'DD/MM/YYYY hh:mm') as MaxTimeStamp

Resident Final

Group By [Order ID];

Drop Field Status;

RENAME Field RequiredStatus to Status;

NoConcatenate

Final2:

Load * Resident Final Where TimeStamp = MaxTimeStamp;

Drop Table Final;

Drop Field MaxTimeStamp;

[hopkinsc]

Hi, thanks for your replies..

I am getting an error but cant see what is wrong.

(i am using the real field names in this btw)

[michael_maeuser]

Order by is only possible in resident load. so first load without order by and then load resident with your order by and drop the first table

[MK_QSL]

Addition to what Michael Replied....

You forgot ; after TIMETAG

It should be

Order By OrderID, TIMETAG;