Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
vpanchuda
Contributor III
Contributor III

Start and end time wrt To event for changing Dimension

HI 

Can someone please help me with below requirement Logic and function to be used.

I need to filter by JOB and Task with start and end time for JO  and event respectively.

Log Table:

datetimeEvent
15-07-20191.00 AmJob Start:Turning
15-07-20192.00 AmTask Start: Turning Componrnt 1 load tool
15-07-20192.30 Amtool loaded
15-07-20192.40 AmMachining
15-07-20193.00 AmTask End: Turning Componrnt 1 load tool
15-07-20193.30 AmTask Start: Turning Componrnt 2 load tool
15-07-20194.00 Amtool loaded
15-07-20195.00 AmMachining
15-07-20195.15 AmTask End:Turning Componrnt 2 load tool
15-07-20195.55 AmJob End: Turning
15-07-20196.12 AmJob Start:Milling
15-07-20196.30 AmTask Start: Milling Componrnt 1 load tool
15-07-20197.00 Amtool loaded
15-07-20197.20 AmMachining
15-07-20197.25 AmTask End:Milling Componrnt 1 load tool
15-07-20197.40 AmTask Start: Milling Componrnt 2 load tool
15-07-20197.50 Amtool loaded
15-07-20198.05 AmMachining
15-07-20198.15 AmTask End: Milling Componrnt 2 load tool
15-07-20198.18 AmJob End: Milling
15-07-20198.30 AmJob Start :Cleaning
15-07-20198.35 AMTask Start: CleaningComponrnt 2 load tool
15-07-20198.40 Amtool loaded
15-07-20198.45 AmCleaning
15-07-20198.45 AmTask End: CleaningComponrnt 2 load tool
15-07-20198.45 AmJob End:Cleaning

 

I need to get table similar so that i can filter in the front end by Job and Task

datetimeEventJOBStart TimeEnd Time
15-07-20191.00 AmJob Start:TurningTurning1.00 Am5.55 Am
15-07-20192.00 AmTask Start: Turning Componrnt 1 load toolTurning1.00 Am5.55 Am
15-07-20192.30 Amtool loadedTurning1.00 Am5.55 Am
15-07-20192.40 AmMachiningTurning1.00 Am5.55 Am
15-07-20193.00 AmTask End: Turning Componrnt 1 load toolTurning1.00 Am5.55 Am
15-07-20193.30 AmTask Start: Turning Componrnt 2 load toolTurning1.00 Am5.55 Am
15-07-20194.00 Amtool loadedTurning1.00 Am5.55 Am
15-07-20195.00 AmMachiningTurning1.00 Am5.55 Am
15-07-20195.15 AmTask End:Turning Componrnt 2 load toolTurning1.00 Am5.55 Am
15-07-20195.55 AmJob End: TurningTurning1.00 Am5.55 Am
15-07-20196.12 AmJob Start:MillingMilling6.12 Am8.18 Am
15-07-20196.30 AmTask Start: Milling Componrnt 1 load toolMilling6.12 Am8.18 Am
15-07-20197.00 Amtool loadedMilling6.12 Am8.18 Am
15-07-20197.20 AmMachiningMilling6.12 Am8.18 Am
15-07-20197.25 AmTask End:Milling Componrnt 1 load toolMilling6.12 Am8.18 Am
15-07-20197.40 AmTask Start: Milling Componrnt 2 load toolMilling6.12 Am8.18 Am
15-07-20197.50 Amtool loadedMilling6.12 Am8.18 Am
15-07-20198.05 AmMachiningMilling6.12 Am8.18 Am
15-07-20198.15 AmTask End: Milling Componrnt 2 load toolMilling6.12 Am8.18 Am
15-07-20198.18 AmJob End: MillingMilling6.12 Am8.18 Am
15-07-20198.30 AmJob Start :CleaningCleaning8.30 Am8.45 Am
15-07-20198.35 AMTask Start: CleaningComponrnt 2 load toolCleaning8.30 Am8.45 Am
15-07-20198.40 Amtool loadedCleaning8.30 Am8.45 Am
15-07-20198.45 AmCleaningCleaning8.30 Am8.45 Am
15-07-20198.45 AmTask End: CleaningComponrnt 2 load toolCleaning8.30 Am8.45 Am
15-07-20198.45 AmJob End:CleaningCleaning8.30 Am8.45 Am

 

Labels (1)
3 Replies
Lauri
Specialist
Specialist

Have you tried calculating start and end time during the data load? Assuming only one JOB runs at a time...

mahaveerbiraj
Creator II
Creator II

Hi ,

I would say  do group by Job and take MinDate(with timestamp ) as Start date  and MaxDate(with timestamp) as EndDate, and take start time from start date and End time from EndDate 

vpanchuda
Contributor III
Contributor III
Author

Thank you for the reply.

Grouped the job using Row num and Peek function , made a different table for end time and it worked got helped by Petter in the community.