Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anthony_Bell
New Member

Identify penultimate value from a tale based on final event string & date

I have a table ('M Jobs') with job reference numbers ([DeptJobNumber]), Job Events ([JobEventDescription]) and Event Times ([JobEventDateTime] which holds the lifecycles of jobs identified by Events and their timestamps.

 

I also have a list of specific event strings, which I need to use to identify the last occurance of each of these events prior to the job being completed (the "Practically Complete" event).

 

For Example, a job starts it's life once it has been "ENTERED" and is being measured until it hits the "PRACTICALLY COMPLETE" event.

I need to pull the last occurance of a listed event prior to the job being completed.

 

In the example below, we can see that the last listed event prior to the job being "Practically Complete" (10/01/2020 16:24:59) was "Gas No Access NTI" (10/01/2020 15:55:38).

 

I would therefore need to pull this event and time along with the job number, start date and completion date.

 

I have attempted to do this by creating a summarised table but I am still not getting the correct results (see below). I have attempted to use || to define the specific strings but the query fails when trying to convert the string to a boolean.

 

Please, can someone help me out with this as I'm having a nightmare!!  

 

Summary Table

Summary Table.PNG

 

Specific Events List

Measured Events.png

 

Jobs & Events Table

Job Lifecycle.PNG

0 REPLIES 0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.