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]

Unfortunately I don't really know what's causing the issue, and I won't really have time to look into it any further until next week, so I'm hoping someone else can help you...

rwunderlich‌

swuehl‌

stalwar1‌

Not sure if these are the right people to tag, but they are names I see around here a lot. Does anyone have any idea why his Autonumber isn't starting at 1 for every JobName?

[sunny_talwar]

Is JobName the only selection you have made in your dashboard when you took the screenshot? Is it possible you have other selections (may be in hidden fields) which are hiding the rows where the Event is 1, 2, 3 in the above screenshot?

[ashis]

Hi Sunny,

Jobname and date and job description are my filter criteria.

Jobname are unique to their description. 

Same jobname would not belong to different job description. 

[swuehl]

Does this mean you have an active selection on date?

[ashis]

Hi Stefan,

Yes, I should select a date , because this report is all about , in a day what time jobs is starting and what time job is ending .

Thank you,

Ashis

[swuehl]

The question is:

Did you have an active selection on Date field, precisely on 06-Sep-2018, when you were taking the screenshot?

If you make a selection on a specific date, you shouldn't be surprised that the sequence numbers don't start on 1, in the case where there are records on a previous date excluded by your current selection.

The sequence number generation is made in the script, hence won't be updated when making selections.

If you want to restart your sequence with 1 for a new Jobname or new date for a given Jobname, then you can add the Date to the autonumber second argument, something like

AutoNumber(tmpEvent, JobName & Date) as Event

[ashis]

Thank you Stefan. I believe that is what happening here.

It is giving sequence if I put

AutoNumber(tmpEvent, JobName & Date) as Event 

[sunny_talwar]

Please mark appropriate responses as correct and helpful instead of marking your own response as helpful

Best,

Sunny

[ashis]

Hi Sunny,

Thank you for pointing it , I did my answer helpful by mistake .

I have marked Stefan's answer as helpful.

[sunny_talwar]

Thanks ashissau‌!!