topic Re: Missing date for Graph in QlikView

Missing date for Graph

tomhovens — Thu, 22 Jan 2015 14:49:32 GMT

Dear Qlikviewers,

I'm looking for a solution to the next problem:

StartDate (D/M/YYYY)	EndDate (D/M/YYYY)	Quantity
1-1-2015	10-1-2015	5
5-1-2015	10-1-2015	2
8-1-2015	11-1-2015	3

How to transform this table to a graph that has to following value

Date (D/M/YYYY)	Quantity
1-1-2015	5
2-1-2015	5
3-1-2015	5
4-1-2015	5
5-1-2015	7
6-1-2015	7
7-1-2015	7
8-1-2015	10
9-1-2015	10
10-1-2015	10
11-1-2015	3

Re: Missing date for Graph

Gysbert_Wassenaar — Thu, 22 Jan 2015 14:54:51 GMT

See this blog post: How to populate a sparsely populated field

Re: Missing date for Graph

tomhovens — Thu, 22 Jan 2015 15:07:07 GMT

is there a way to not reload the script?

Re: Missing date for Graph

Gysbert_Wassenaar — Thu, 22 Jan 2015 19:04:56 GMT

Probably, but that cure is probably worse than the disease.

Re: Missing date for Graph

jerem1234 — Thu, 22 Jan 2015 19:58:33 GMT

Here's it without script using your example. Uses Valueloop and other oddities (for some reason date around valueloop doesn't work in the dimension, but works in expression, so had to hide the dimension column). You are better off going with script; much cleaner and flexible. I'd listen to Gysbert.

Hope this helps!

Re: Missing date for Graph

MarcoWedel — Fri, 23 Jan 2015 00:31:15 GMT

Hi,

I totally agree that you should change your script and reload your data.

Another solution doing so could be:

table1:
LOAD *,
    AutoNumberHash128([StartDate (D/M/YYYY)], [EndDate (D/M/YYYY)]) as %ID
FROM [http://community.qlik.com/thread/149294] (html, codepage is 1252, embedded labels, table is @1);

tabCalendar:
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1<=MaxDate;
LOAD Min([StartDate (D/M/YYYY)]) as MinDate,
    Max([EndDate (D/M/YYYY)]) as MaxDate
Resident table1;

tabLink:
IntervalMatch(Date)
LOAD [StartDate (D/M/YYYY)], [EndDate (D/M/YYYY)]
Resident table1;

Left Join (tabLink)
LOAD Distinct
  [StartDate (D/M/YYYY)],
  [EndDate (D/M/YYYY)],
  AutoNumberHash128([StartDate (D/M/YYYY)], [EndDate (D/M/YYYY)]) as %ID
Resident tabLink;

DROP Fields [StartDate (D/M/YYYY)], [EndDate (D/M/YYYY)] From tabLink;

hope this helps

regards

Marco