Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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;
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;
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)
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
Addition to what Michael Replied....
You forgot ; after TIMETAG
It should be
Order By OrderID, TIMETAG;