Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
alexandermllr
New Contributor II

Qlikview - fill up data between timestamp

Hello,

this is my data format:

Car

FromTostatus
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
214.02.2017 01:00

16.02.2017 08:00

not available
2

05.05.2017 03:01

05.05.2017 22:00not available

And i want to have this:

CarFromTo

status

1

05.01.2017 00:00

05.01.2017 05:00available
105.01.2017 05:0005.01.2017 09:00not available
105.01.2017 09:0005.01.2017 23:59available

I want a 24 hour overview of every car.

I hope someone can help me.

Thanks

1 Reply
ahaahaaha
Honored Contributor

Re: Qlikview - fill up data between timestamp

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

1.jpg

2.jpg

Regards,

Andrey

Community Browser