Display all data from the same loading based on a shift.

[Gabriel-Albuquerque]

Hello, I'm having difficulties with a solution for displaying some data. Let me explain my problem.

I need to display a timeline of events that occur during a truck's loading process at the factory. However, some events can repeat, and my main piece of information is the occurrence called "Truck released for entry into the factory." From this point onwards, I need all information with the same loading code to appear in the same shift. If my main piece of information indicates that it's in Shift C, I need the rest to be displayed in Shift C, regardless of the shift in which it ended.

If I haven't explained it clearly, please let me know, and I'll try again.

[rubenmarin · Accepted solution]

Hi, you can use a mapping table that stores the first shift for each loading code with that ocurrence. And use this mapping table to assign the shift instead of loading it.

mapShift:
Mapping LOAD LoadingCode, Shift
From Table
Where occurrence="TheText"
Order by Loadingcode, Sequence;

Data:
LOAD 
  Field1,
  Field2,...
  ApplyMap(mapShift,LoadingCode) as Shift
From Table;

[tealowk]

Hi, 

you can add a field in the load script that checks if the event is equal to "Truck cleared for entry" and then assigns either way the original shift or the shift in which the truck was cleared. 

Assuming that the data is loaded in the correct chronological order, this example assigns the shift based on your requirements.

Test:
Load *,
	If(Event = 'Truck cleared for entry', Shift, Peek(Assigned_Shift)) as Assigned_Shift
;
Load * Inline [
Event, Sequence, Shift
Truck loaded, 1, A
Truck inspected, 2, A
Truck cleared for entry, 3, B
Truck at dock, 4, C
Truck unloaded, 5, C
Truck loaded, 6, C
Truck inspected, 7, C
Truck cleared for entry, 8, A
Truck at dock, 9, A
Truck unloaded, 10, A
];

Until the first 'Truck cleared for entry' value appears, the assigned shift will be null. You can easily replace a null value in the Assigned_Shift field with the original Shift value, in case that would matter.

The result of my test table looks as follows:

Hope that helps.

Kind regards from Brussels,

Thilo

[Gabriel-Albuquerque]

Hi Ruben, thank you so much for explaining, that really solved my problem. Big hug!