How to create sequence based on start time for gantt chart

[ashis]

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

‌

[jensmunnichs · Accepted solution]

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]

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]

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]

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]

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

[jensmunnichs]

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]

Hi Jens,

Cant really upload qvw due to security .

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

Thank you,

[jensmunnichs]

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]

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