A Continuous Chart Line

[#1]

Hi,

As seen in the graph above, the blue line is shorther than the red line. However, I know that if the blue line is broken like this one above, it means there is no value added to the database table, no change, so that at the week of 27 th the blue line value is still 640.

So, I wonder if there is a way to have a continuous blue line which is completed after the break. And if it is so, do you recommend to do it at SQL, QV or chart side.

Thanks in advance.

[#2]

I would use a left join (either in SQL or QV load script) to create a table that had null values for the missing data points. Then you can use QV previous function to replace the null values with the previous value.

in your example the left join would look liek this:

week, dev

22,640
23,443
24,344
25,530
26,640
27,NULL

then you can use isnull and previous to change row 27 to
27,640

Steve

[#3]

Thanks for the input.

On the other hand, there is one more consideration I have missed to express. The blue line must be as long as the red one. In the case above there is only one missing week data. What if there is two or more weeks data to be compeleted. it is noted that the red line's data is fed by a daily job running everyday in SQL Server.

[#4]

A left join provides a value for ALL rows in the "left" table.

so in your case if you have weeks 20-27, but you data looks like this:

22,a
24,b
27,c

Then when you join the weeks table with the data table you get
20,null
21,null
22,a
23,null
24,b
25,null
26,null
27,c

A left join (e.g. table a left join table b) means include all rows from the left table plus all matching data from the right table. You should find a good description plus examples of outer joins (of which left is one type) on wikipedia, or similar.

Steve

[#5]

Thanks for the advice.

I have solved the issue in another way around.

[#6]

Can you explain how you solved that issue.

Thanks in Advance.

-Naveen

[#7]

In my case, I have a field in a table and from the string pattern of the field value I parse the table rows as "Dev" or "Main". An automatic job's results populate the Main type values however Dev is initiated by developers so it is not continuous till today. And this was a problem to compare the two type values in a graph chart.

To solve it, in SQL side, I created an sql script which finds the last values for Dev type data then in a loop I duplicated the last day's value for each day until today thus created dummy rows, then concatenated them to my main table. Now the chart is complete.