topic Re: How to create sequence based on start time for gantt chart in QlikView

How to create sequence based on start time for gantt chart

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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

‌

Re: How to create sequence based on start time for gantt chart

jensmunnichs — Fri, 12 Oct 2018 12:06:24 GMT

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

Re: How to create sequence based on start time for gantt chart

ashis — Fri, 12 Oct 2018 13:56:06 GMT

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.

Re: How to create sequence based on start time for gantt chart

ashis — Fri, 12 Oct 2018 14:02:45 GMT

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,

Re: How to create sequence based on start time for gantt chart

jensmunnichs — Fri, 12 Oct 2018 14:05:44 GMT

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?

Re: How to create sequence based on start time for gantt chart

ashis — Fri, 12 Oct 2018 14:15:49 GMT

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

Re: How to create sequence based on start time for gantt chart

jensmunnichs — Fri, 12 Oct 2018 14:26:10 GMT

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.

Re: How to create sequence based on start time for gantt chart

ashis — Fri, 12 Oct 2018 14:29:13 GMT

Hi Jens,

Cant really upload qvw due to security .

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

Thank you,

Re: How to create sequence based on start time for gantt chart

jensmunnichs — Fri, 12 Oct 2018 14:33:53 GMT

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.

Re: How to create sequence based on start time for gantt chart

ashis — Fri, 12 Oct 2018 14:43:26 GMT

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

Re: How to create sequence based on start time for gantt chart

jensmunnichs — Fri, 12 Oct 2018 14:52:00 GMT

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?

Re: How to create sequence based on start time for gantt chart

sunny_talwar — Fri, 12 Oct 2018 15:09:12 GMT

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?

Re: How to create sequence based on start time for gantt chart

ashis — Fri, 12 Oct 2018 17:06:16 GMT

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.

Re: How to create sequence based on start time for gantt chart

swuehl — Fri, 12 Oct 2018 19:05:43 GMT

Does this mean you have an active selection on date?

Re: How to create sequence based on start time for gantt chart

ashis — Mon, 15 Oct 2018 05:53:15 GMT

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

Re: How to create sequence based on start time for gantt chart

swuehl — Mon, 15 Oct 2018 07:36:32 GMT

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

Re: How to create sequence based on start time for gantt chart

ashis — Mon, 15 Oct 2018 08:48:20 GMT

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

It is giving sequence if I put

AutoNumber(tmpEvent, JobName & Date) as Event

Re: How to create sequence based on start time for gantt chart

sunny_talwar — Mon, 15 Oct 2018 11:01:17 GMT

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

Best,

Sunny

Re: How to create sequence based on start time for gantt chart

ashis — Tue, 16 Oct 2018 04:53:01 GMT

Hi Sunny,

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

I have marked Stefan's answer as helpful.

Re: How to create sequence based on start time for gantt chart

sunny_talwar — Tue, 16 Oct 2018 11:20:14 GMT

Thanks ashissau‌!!