Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
ashis
Creator III
Creator III

How to create sequence based on start time for gantt chart

Hi ,

I have few jobs that are running few times in a day. In my data model I have jobs start time and end time.

I have stack gantt chart where I am plotting this information .

So I need to create jobs sequence based on the jobs start time.

My load script looks like the following:

Spike:
LOAD *,
Interval(End_Time - Start_Time,'mm:ss') as Diff;

LOAD JobName,
     ....
  .....
     date(Start_Time,'DD-MM-YYYY hh:mm:ss') as Start_Time,
     date(End_Time,'DD-MM-YYYY hh:mm:ss') as End_Time,
  DATE(FLOOR(Start_Time), 'DD-MMM-YY') as Date
    
FROM
[..\xxx.qvd]
(qvd);


Temp1:
load *,
RowNo() as tmpEvent
Resident Spike;

Test:
NoConcatenate
LOAD *,
AutoNumber(tmpEvent, JobName) as Event
Resident Temp1;
Drop Field tmpEvent;

Drop table Temp1;
drop table Spike;

My gantt chart looks like the following: Nothing to be modified in the chart , it looks good.

The Event field is giving me jobs sequence.

However I noticed that it is not giving correct sequence based on the start time of the job, if I  plot straight table and get job  , start time, end time, and Event.

Could you please help me how do I get this Event based on the jobs start time. So if the job start first the sequence would be 1 and then subsequently it would show 2,3,.....N

Currently it is showing like the following:

Thank you,

Ashis

1 Solution

Accepted Solutions
jensmunnichs
Creator III
Creator III

Hey, I think I helped you with this last time.

I think I made the wrong assumption last time I helped you with this that your data would load in in order, so there would be no need to sort the data before adding the event numbers.

I think this should be fixed fairly easily by adding Order By to your Test table:

Test:

NoConcatenate

LOAD *,

AutoNumber(tmpEvent, JobName) as Event

Resident Temp1

Order By Start_Time asc;

This makes sure the start times are always sorted from earliest to latest so your most recent event will always be the highest Event number.

Let me know if it works

View solution in original post

20 Replies
jensmunnichs
Creator III
Creator III

Hey, I think I helped you with this last time.

I think I made the wrong assumption last time I helped you with this that your data would load in in order, so there would be no need to sort the data before adding the event numbers.

I think this should be fixed fairly easily by adding Order By to your Test table:

Test:

NoConcatenate

LOAD *,

AutoNumber(tmpEvent, JobName) as Event

Resident Temp1

Order By Start_Time asc;

This makes sure the start times are always sorted from earliest to latest so your most recent event will always be the highest Event number.

Let me know if it works

ashis
Creator III
Creator III
Author

Hi Jens,

Yes, that's correct you are the one who helped me to get this. I was trying to tag you in my post however did not find that option.

Thank you so much for helping me. I appreciate your time and effort.

if I put order by I am getting the following result that is correct .

However i was thinking if i get this result in ascending order starting from 1 ......N .

I tried order by JobName and Start_time  but the same result.

Any thought.

ashis
Creator III
Creator III
Author

Surprising my for few jobs the event starting just as the image above 8 ,9,10......N

However i just noticed for some it is showing as expected . 1.2,3....N.

Thank you,

jensmunnichs
Creator III
Creator III

Could you create part of the table you want in Excel to make it a bit more clear what the problem is? Or is it just that the 'Event' numbering doesn't start at 1 for every job?

ashis
Creator III
Creator III
Author

Yes, the Event number does not start at 1 for every job.

jensmunnichs
Creator III
Creator III

That is rather strange... any chance you could post your (reloaded) .qvw?

I feel like the only way this would happen is if the Jobname is the same as another job before... But it's hard to tell without seeing your data.

ashis
Creator III
Creator III
Author

Hi Jens,

Cant really upload qvw due to security .

Could be you are right, I will investigate on this.

Thank you,

jensmunnichs
Creator III
Creator III

I figured as much, understandable.

Maybe try commenting(/removing) the 'Drop field tmpEvent' in your script and adding that column to the table you sent a screenshot of earlier... That might clear some things up.

ashis
Creator III
Creator III
Author

No , tmpEvent field does not work , it is giving similar output.