time slices (trend diagram)

[markus19-85]

Hello,

I have the following table:

it only Shows the time slices of each state.

I would like to create a trend diagram, to the the whole process of an Issue. From the first Valid_from till today

to get the right solution I need every single date out of the time slice till today

For example like this

It is possible to solve this Problem in QlikView script? That would be greate

Thank you.

[sunny_talwar]

I think you need to create a master calendar using the min date from Valid_from field and max date from valid_to field and then use interval match function to achieve what you are looking for.  About IntervalMatch you can see the following blog: IntervalMatch

HTH

Best,

Sunny

[maxgro]

you can find some help in this post and doc (technical brief at the end of the post)

How to populate a sparsely populated field

[sunny_talwar]

Try this script:

Table:

LOAD Issue_ID,

    Valid_from,

    Valid_until,

    State

FROM

[test (2).xlsx]

(ooxml, embedded labels, table is Tabelle2);

MinMax:

LOAD Min(Valid_from) as minDate,

  Today() as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table MinMax; 

Calendar: 

LOAD Date($(varMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

IntervalMatch:

IntervalMatch(Date)

LOAD Valid_from,

  Valid_until

Resident Table;

Ouput:

[markus19-85]

thank you, thats looks good.

Is there a way to get ride of the syntatic keys?

[sunny_talwar · Accepted solution]

Left Join IntervalMatch table to Table like this:

Table:

LOAD Issue_ID,

    Valid_from,

    Valid_until,

    State

FROM

[test (2).xlsx]

(ooxml, embedded labels, table is Tabelle2);

MinMax:

LOAD Min(Valid_from) as minDate,

  Today() as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table MinMax; 

Calendar: 

LOAD Date($(varMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Left Join (Table)

IntervalMatch(Date)

LOAD Valid_from,

  Valid_until

Resident Table;