reshape table scripting

[qlikviewalex]

Hi,

I load a Table.

Tasks:

LOAD [Object-ID],

     Key,

     start,

     end

FROM

test.xlsx

(ooxml, embedded labels, table is table1);

This Table looks like this:

key	start	end
K1	2.1.2016	3.1.16
K2	1.1.2016	5.1.16
K3	7.1.2016	7.1.16
K4	5.1.2016	8.1.16

now I need a table with only one date and all the keys of the tasks that are open on that day.

The first day should be the first start day. The last day should be today.

The condition is:

date >= start and date < end

And I need a 3. column with the Information "open"

So my example should look like this:

Date	Key	status
1.1.16	K1	open
2.1.16	K1	open
2.1.16	K2	open
3.1.16	K2	open
4.1.16	K2	open
5.1.16	K4	open
6.1.16	K4	open
7.1.16	K4	open

Unfortunately, I have no idea how I can do this reshaping.

I hope someone can help me.

Alex

[sunny_talwar · Accepted solution]

I think the sample output you have provided isn't correct, or may be I am missing something here. See if this is right

Table:

LOAD key,

  Date(start + IterNo() - 1) as Date,

  'Open' as Status

While start + IterNo() - 1 < end;

LOAD key,

    start,

    end

FROM

[https://community.qlik.com/thread/230851]

(html, codepage is 1252, embedded labels, table is @1);

[qlikviewalex]

Thanks.

It works well.