1 Reply Latest reply: Jul 24, 2017 10:05 AM by Andrey Khoronenko RSS

    Qlikview - fill up data between timestamp

    Alexander Müller

      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

        • Re: Qlikview - fill up data between timestamp
          Andrey Khoronenko

          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