Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 
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
