topic Re: Gantt chart giving distinct value in QlikView

Gantt chart giving distinct value

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

Hi,

I have a gantt chart where I am taking jobname and job description as 2 dimensions and one expression that is

=max(endtime) - min(starttime)

I noticed there is one job which run twice so I am expecting two bars in the gantt chart, however it is showing only one bar. I want it to show two bars instead one .

If I take a table box and take same above 2 dimensions , it will give me two rows.

Please help me, what mistake I am doing here. How do I get two bars in the above gantt chart.

Re: Gantt chart giving distinct value

jensmunnichs — Thu, 04 Oct 2018 11:53:39 GMT

Since you've used job description + name as dimensions, and these records have the same description/name, it will use both in the expression.

So when you're using max(endtime) - min(starttime), it will take the maximum endtime from both records (6-9-2018 1:33:03), and the minimum starttime from both records (6-9-2018 00:19:30), and you'll end up with 1 number for both records, as they're grouped together.

If you want 2 seperate bars, not on the same horizontal line, you'll either have to change the name/description of one of the 2 records so they're not grouped together anymore, or I guess you could use a JobID or something if you don't want to change the name/description.

If you want them both on the same horizontal line, I'm not sure if you'll be able to use a bar chart, maybe using a stacked bar chart by hiding the middle bit where the job is not being executed, but I'm not sure. If you post a sample of your data + the chart you've made so far I might be able to help.

Edit: In case you want to use a stacked bar chart, have a look at this link: Files for creation of a Gantt chart

The final tab in the example shows how to create a gantt chart using a stacked bar chart.

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 05:55:01 GMT

Hi Jens,

Thank you so much for your guidance. I am trying to create similar chart in the last tab as displayed in the above link.

For expression I am using

=RangeMax(above(Max (End_Time),0,rowno())) // Upper limit

RangeMax(Min (Start_Time),RangeMax(above(Max (End_Time),1,rowno()-1))) // Lower limit

offset

=if(IsNull(above(Only(JobName))), Min(Start_Time),

RangeMax(0,Min(End_Time)-RangeMax(above(Max(End_Time),1,rowno()-1))))

however the result is same. getting two bars only.

Re: Gantt chart giving distinct value

jensmunnichs — Fri, 05 Oct 2018 06:45:39 GMT

Hey,

First off, again, if you could post a sample of your data and the chart you've made so far, it'd be a lot easier to identify where you're going wrong. If your data is confidential, just load an inline table with the same data but replacing the confidential data, for example Job name could be A, B, C, etc. and description could be a, b, c, etc.

If you're using the method described in the link, you'll still have to differentiate between the 2 records of the same job name. In the example they split up every phase of every project into events. In your case, the project field, used as the first dimension, is the JobName for you, as you already identified when modifying the expression. The second dimension, eVent*, does not exist for you yet.

You could either change your job descriptions to include a number for every iteration of the same job (so JobName = A, description = a1, JobName = A, description = a2 for example), or you could create a separate column for this number, though then you wouldn't be able to show the description in the chart (unless you combine JobName and description).

Add JobName as your first dimension and description or event (depending on where you made the distinction) as your second dimension, make sure all the settings are the same as the example** (static min and max in scale for the axes is an important one), and see if that does it for you.

*Note that in the example there is an event and an eVent field. This is because the stacked bar chart does not differentiate between phases, just events, but some of the other charts differentiate between phases AND events.

**I think I spotted a mistake in your offset expression, and you might've already done all of the above and this is the only fix you need, but I already typed it all out before I spotted it so I'm going to leave it anyway. See below:

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 06:55:19 GMT

Jens,

Thank you for spotting this typo. However it did not fixed it. I will send you the qvw file shortly.

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 07:21:03 GMT

Hi Jens,

I found out the problem. Currently what is happening , if in a given date , if I have one job ran twice, it is unable to figure out or distinguish because the name is same hence it is accumulating and giving one bar instead of two bars.

In my sample data , I have introduce one column and created sequence based on the time. Like the screen shot.

then bringing this Event column in my chart , then it is giving me two bars.

Now my question is how do I produce the sequence or event in my real data in the load script.

Any idea , please help.

Re: Gantt chart giving distinct value

jensmunnichs — Fri, 05 Oct 2018 07:53:04 GMT

Hey,

Sorry it took a while, check the script in the attached file.

Basically, you want to load an extra column with the original data, RowNo(). Then you (Noconcatenate) load the same data, but with Autonumber(tmpEvent, JobName) as Event. This will give every event of the same job a new number, restarting the count when it gets to a new JobName. Then you drop the original table and the RowNo() field, as you no longer need them. Check out the table preview in the data model to see the result.

So in your case you simply replace the load of the first table with a load of your data, making sure to keep the RowNo() as tmpEvent.

This is pretty much what's being done in the example in the link, but the example might be a bit confusing as there are a lot of things going on to create the data used in the example.

If you need any more help, let me know If you feel like you have the answer you need, make sure to mark the comment that answered your question as the correct answer so people know you don't need any more help.

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 08:17:12 GMT

Hi Jens,

Could you please attached the file you have mentioned.

Thank you so much for your guidance.

Re: Gantt chart giving distinct value

jensmunnichs — Fri, 05 Oct 2018 08:24:25 GMT

Hey,

Can you not see it? Are you viewing the thread from your inbox? If so, use the link below and you should be able to see the file under my comment. In case it didn't work for some reason, I've attached the file to this comment as well.

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 08:26:44 GMT

Yes , I was seeing from inbox. Thank you so much.

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 09:11:57 GMT

Hi Jens,

Sorry for bothering you again and again. I am little confused where to put this autonumber. In my data model I do have one link table . My load script has following script.

Spike1:
LOAD Region &'|'& JobName as key,
Timestamp,
Diff,
//Jobname as JobName,
   DESCRIPTION,
System_ID,
DataFeedSystem_ID,
Job_Type_Descr,
Instance_ID,
EnrichGroupNumber,
EnrichGroupDescr,
Depth,
Start_Time,
End_Time,
Date,
TASKTYPE,
Instance
Resident Spike;

Relationship1:
LOAD Region &'|'& JobName as key,
DependsOnTimestamp,
DependOnDiff,
//JobName,
   DependsON,
DependsON_Descr,
DependsON_Type,
//Start_Time as DependsOnStarttime,
   //End_Time as DependsOnEndtime

DependsOnStarttime ,
DependsOnEndtime
Resident Relationship;

Link:
LOAD
Distinct
Region &'|'& JobName as key ,
JobName as Temp_key,
Region,
JobName
Resident Spike;

Concatenate(Link)
load
Region &'|'& JobName as key ,
JobName as Temp_key,
Region,
JobName
Resident Relationship where not Exists (Temp_key,Region &'|'& JobName);

Drop field Temp_key from Link;

drop table Relationship;
drop table Spike;

Could you please point out where to put your code . I guess it would be in the link table , then it is creating synthetic key.

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 09:16:29 GMT

Re: Gantt chart giving distinct value

jensmunnichs — Fri, 05 Oct 2018 09:32:34 GMT

Hmm.. Is description always unique to a job name? As in, 2 different jobs can never have the same description?

In that case you could add RowNo() to the Spike table, then add the Autonumber function to Spike1, but with description instead of JobName in the function. I think that should work... But honestly, it's hard to tell without being able to try things in your full document.

You would have to use key as a dimension though, instead of JobName... would that be a problem?

EDIT: You could also try using the key in the autonumber function.

Re: Gantt chart giving distinct value

ashis — Fri, 05 Oct 2018 10:10:38 GMT

Thank you Jens,

I am using rowno() in the spike table , which is the first table.

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;

Hope this will work, I am so thankful to you for your guidance. You rock.