Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
vpanchuda
Contributor III
Contributor III
‎2019-07-18 04:02 AM

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)
641 Views
0 Likes
3 Replies
Lauri
Specialist
Specialist
‎2019-07-22 05:06 PM

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

629 Views
0 Likes
mahaveerbiraj
Creator II
Creator II
‎2019-07-23 02:43 AM

In response to Lauri

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 

620 Views
0 Likes
vpanchuda
Contributor III
Contributor III
‎2019-07-23 07:54 AM
Author

In response to mahaveerbiraj

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.

609 Views
0 Likes