Qlikview - fill up data between timestamp

[alexandermllr]

Hello,

this is my data format:

Car	From	To	status
1	05.01.2017 05:00	05.01.2017 09:00	not available
1	31.03.2017 17:12	31.03.2017 20:15	not available
2	14.02.2017 01:00	16.02.2017 08:00	not available
2	05.05.2017 03:01	05.05.2017 22:00	not available

And i want to have this:

Car	From	To	status
1	05.01.2017 00:00	05.01.2017 05:00	available
1	05.01.2017 05:00	05.01.2017 09:00	not available
1	05.01.2017 09:00	05.01.2017 23:59	available

I want a 24 hour overview of every car.

I hope someone can help me.

Thanks

[ahaahaaha]

Hi,

Perhaps is not the best way, but we are not looking for easy ways. Example at attached file.

Car:

LOAD Distinct

Car

FROM

[https://community.qlik.com/thread/268647?sr=inbox&ru=228680]

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

//create Care-autoCalendar

Calendar:

LOAD

Date(Date('01.01.2017') + RecNo() - 1) as CalDate

autogenerate(Today() - Date('01.01.2017') + 1);

Join

LOAD

Time(RecNo()/1440, 'hh:mm') as CalTime

AutoGenerate 1440;

Left Join

LOAD*,

Timestamp(Num(CalDate)+Num(CalTime), 'DD.MM.YYYY hh:mm') as DateTime,

Text(Timestamp(Num(CalDate)+Num(CalTime), 'DD.MM.YYYY hh:mm')) as KeyDateTime

Resident Calendar;

DROP Fields CalDate, CalTime;

Join (Car)

LOAD*

Resident Calendar;

DROP Table Calendar;

//load source data

Table1:

CrossTable(Borders, DateTime1, 2)

LOAD Car as Car1,

    status,

    From,

    To

FROM

[https://community.qlik.com/thread/268647?sr=inbox&ru=228680]

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

Left Join (Car)

LOAD

Car1 as Car,

Text(DateTime1) as KeyDateTime,

status,

Borders

Resident Table1;

DROP Table Table1;

NoConcatenate

Table2:

LOAD

Car,

DateTime,

If(Previous(Borders) = 'From' And not status='not available', 'To', Borders) as Borders,

If(Previous(Borders) = 'From' And not status='not available', 'available', status) as status

Resident Car

Order By Car asc, DateTime desc;

DROP Table Car;

NoConcatenate

Table3:

LOAD

Car,

DateTime,

If((Previous(Borders) = 'To' And not status='not available') or RowNo()=1, 'From', Borders) as Borders,

If((Previous(Borders) = 'To' And not status='not available') Or RowNo()=1, 'available', status) as status

Resident Table2

Order By Car asc, DateTime asc;

DROP Table Table2;

NoConcatenate

Table4:

LOAD

Car,

DateTime,

TimeStamp(Timestamp#(DateTime, 'DD.MM.YYYY hh:mm')) as StartDay1,

DayStart(DateTime) as StartDay2,

TimeStamp(DayEnd(DateTime), 'DD.MM.YYYY hh:mm') as EndDay,

Borders,

If( IsNull( status ), Peek( status ), status ) as status

Resident Table3;

DROP Table Table3;

//fill in the start and end of each day

NoConcatenate

Table5:

LOAD

Car,

DateTime,

If(TimeStamp(Timestamp#(DateTime, 'DD.MM.YYYY hh:mm')) = DayStart(DateTime) And status='available', 'From',

If(Text(DateTime) = Text(TimeStamp(DayEnd(DateTime), 'DD.MM.YYYY hh:mm')) And status='available', 'To', Borders)) as Borders,

status

Resident Table4;

NoConcatenate

Table6:

LOAD*

Resident Table5

Where IsNull(Borders)=0;

DROP Table Table5;

DROP Table Table4;

NoConcatenate

ResultTable:

LOAD

Car,

Previous(DateTime) as From,

DateTime as To,

status

Resident Table6

Where Borders='To';

DROP Table Table6;

Result

Regards,

Andrey