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: 
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:00 AM

Creating start and end time from 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 B 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)
811 Views
0 Likes
1 Solution

Accepted Solutions
Anil_Babu_Samineni
MVP
MVP
‎2019-07-23 09:46 AM

In response to vpanchuda

@vpanchuda 

You should cover or describe that as well to avoid too much effort. Perhaps this?

Sample:
LOAD date,
time,
Event,
If(Len(Trim(JOB)) = 0, Peek('JOB'), JOB) as JOB,
If(Len(Trim([Start Time])) = 0, Peek('Start Time'), [Start Time]) as [Start Time], [End Time];

LOAD *, If(Index(Event, 'Job Start:Turning'),'Turning',
If(Index(Event, 'Job Start:Milling'),'Milling',
If(Index(Event, 'Job Start :Cleaning'),'Cleaning'))) as JOB,
If(Index(Event, 'Job Start:Turning'),time,
If(Index(Event, 'Job Start:Milling'),time,
If(Index(Event, 'Job Start :Cleaning'),time))) as [Start Time],
If(Index(Event, 'Job End'), time) as [End Time] Inline [
date, time, Event
15-07-2019, 01:00 AM, Job Start:Turning
15-07-2019, 02:00 AM, Task Start: Turning Componrnt 1 load tool
15-07-2019, 02:30 AM, tool loaded
15-07-2019, 02:40 AM, Machining
15-07-2019, 03:00 AM, Task End: Turning Componrnt 1 load tool
15-07-2019, 03:30 AM, Task Start: Turning Componrnt 2 load tool
15-07-2019, 04:00 AM, tool loaded
15-07-2019, 05:00 AM, Machining
15-07-2019, 05:15 AM, Task End:Turning Componrnt 2 load tool
15-07-2019, 05:55 AM, Job End: Turning
15-07-2019, 06:12 AM, Job Start:Milling
15-07-2019, 06:30 AM, Task Start: Milling Componrnt 1 load tool
15-07-2019, 07:00 AM, tool loaded
15-07-2019, 07:20 AM, Machining
15-07-2019, 07:25 AM, Task End:Milling Componrnt 1 load tool
15-07-2019, 07:40 AM, Task Start: Milling Componrnt 2 load tool
15-07-2019, 07:50 AM, tool loaded
15-07-2019, 08:05 AM, Machining
15-07-2019, 08:15 AM, Task End: Milling Componrnt 2 load tool
15-07-2019, 08:18 AM, Job End: Milling
15-07-2019, 08:30 AM, Job Start :Cleaning
15-07-2019, 08:35 AM, Task Start: CleaningComponrnt 2 load tool
15-07-2019, 08:40 AM, tool loaded
15-07-2019, 08:45 AM, Cleaning
15-07-2019, 08:45 AM, Task End: CleaningComponrnt 2 load tool
15-07-2019, 08:45 AM, Job End:Cleaning
];

Final:
NoConcatenate
LOAD date,
time,
Event,
JOB,
[Start Time],
If(Len(Trim([End Time])) = 0, Peek('End Time'), [End Time]) as [End Time]
Resident Sample Order By time desc, Event asc, JOB desc, [Start Time] desc;

DROP Table Sample;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

710 Views
0 Likes
3 Replies
Anil_Babu_Samineni
MVP
MVP
‎2019-07-20 07:59 AM

PFA

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Test_M141450.qvw
800 Views
0 Likes
vpanchuda
Contributor III
Contributor III
‎2019-07-21 08:50 AM
Author

In response to Anil_Babu_Samineni

HI Anil 

Thanks for the reply ,I had used similar function and able to achieve start time for job and task but my problem is the end time of a job and task.

729 Views
0 Likes
Anil_Babu_Samineni
MVP
MVP
‎2019-07-23 09:46 AM

In response to vpanchuda

@vpanchuda 

You should cover or describe that as well to avoid too much effort. Perhaps this?

Sample:
LOAD date,
time,
Event,
If(Len(Trim(JOB)) = 0, Peek('JOB'), JOB) as JOB,
If(Len(Trim([Start Time])) = 0, Peek('Start Time'), [Start Time]) as [Start Time], [End Time];

LOAD *, If(Index(Event, 'Job Start:Turning'),'Turning',
If(Index(Event, 'Job Start:Milling'),'Milling',
If(Index(Event, 'Job Start :Cleaning'),'Cleaning'))) as JOB,
If(Index(Event, 'Job Start:Turning'),time,
If(Index(Event, 'Job Start:Milling'),time,
If(Index(Event, 'Job Start :Cleaning'),time))) as [Start Time],
If(Index(Event, 'Job End'), time) as [End Time] Inline [
date, time, Event
15-07-2019, 01:00 AM, Job Start:Turning
15-07-2019, 02:00 AM, Task Start: Turning Componrnt 1 load tool
15-07-2019, 02:30 AM, tool loaded
15-07-2019, 02:40 AM, Machining
15-07-2019, 03:00 AM, Task End: Turning Componrnt 1 load tool
15-07-2019, 03:30 AM, Task Start: Turning Componrnt 2 load tool
15-07-2019, 04:00 AM, tool loaded
15-07-2019, 05:00 AM, Machining
15-07-2019, 05:15 AM, Task End:Turning Componrnt 2 load tool
15-07-2019, 05:55 AM, Job End: Turning
15-07-2019, 06:12 AM, Job Start:Milling
15-07-2019, 06:30 AM, Task Start: Milling Componrnt 1 load tool
15-07-2019, 07:00 AM, tool loaded
15-07-2019, 07:20 AM, Machining
15-07-2019, 07:25 AM, Task End:Milling Componrnt 1 load tool
15-07-2019, 07:40 AM, Task Start: Milling Componrnt 2 load tool
15-07-2019, 07:50 AM, tool loaded
15-07-2019, 08:05 AM, Machining
15-07-2019, 08:15 AM, Task End: Milling Componrnt 2 load tool
15-07-2019, 08:18 AM, Job End: Milling
15-07-2019, 08:30 AM, Job Start :Cleaning
15-07-2019, 08:35 AM, Task Start: CleaningComponrnt 2 load tool
15-07-2019, 08:40 AM, tool loaded
15-07-2019, 08:45 AM, Cleaning
15-07-2019, 08:45 AM, Task End: CleaningComponrnt 2 load tool
15-07-2019, 08:45 AM, Job End:Cleaning
];

Final:
NoConcatenate
LOAD date,
time,
Event,
JOB,
[Start Time],
If(Len(Trim([End Time])) = 0, Peek('End Time'), [End Time]) as [End Time]
Resident Sample Order By time desc, Event asc, JOB desc, [Start Time] desc;

DROP Table Sample;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
711 Views
0 Likes