6 Replies Latest reply: Jan 8, 2016 6:49 PM by Marco Wedel RSS

    Time stamp transformation

    marcin szuberla

      Hi and Happy New Year (almost)!

       

      I have a question, I hope, someone will be able to help me with.

      I am working with a table of data, please see attached data sample, which I need to transform (preferable, via script) to look like the second attachment.

      1.PNG

      2.PNG

       

      Basically, I need to be able to figure out, whether the site was open during currently selected hours of the day, and the number of minutes the site was open during that time.

       

      Any help is very appreciated!

       

      Marcin

        • Re: Time stamp transformation
          Massimo Grossi

          try this (you have to make some change for the non full hours, ie 23:59, 16:30 etc.....) in the if

           

          SET TimeFormat='hh:mm:ss';

           

          INPUT:

          load * Inline [

          key_CustomerSiteHours,Site,ScannerType,WeekDay,Open,Close

          26,10_2,CT,Monday,00:00:00,23:59:00

          27,10_2,CT,Tuesday,00:00:00,23:59:00

          28,10_2,CT,Wednesday,00:00:00,23:59:00

          29,10_2,CT,Thursday,00:00:00,23:59:00

          30,10_2,CT,Friday,00:00:00,23:59:00

          31,10_2,CT,Saturday,00:00:00,23:59:00

          32,10_2,CT,Sunday,00:00:00,23:59:00

          33,10_2,US,Monday,00:00:00,23:59:00

          34,10_2,US,Tuesday,00:00:00,23:59:00

          35,10_2,US,Wednesday,00:00:00,23:59:00

          36,10_2,US,Thursday,00:00:00,23:59:00

          37,10_2,US,Friday,00:00:00,23:59:00

          38,10_2,US,Saturday,00:00:00,23:59:00

          39,10_2,US,Sunday,00:00:00,23:59:00

          47,10_2,MR,Sunday,06:00:00,22:00:00

          54,10_2,NM,Sunday,06:00:00,16:30:00

          ];

           

          For i = 0 to 23

            left join (INPUT) LOAD

            key_CustomerSiteHours,

            if(Time#($(i), 'h')>=Time#(Open) and Time#($(i), 'h')<=Time#(Close),

            Time(Time#(1,'h'),'h:mm'),

            Time(Time#(0,'h'),'h:mm')) as [$(i):00]

            Resident INPUT;

          Next;

           

           

          1.png

          • Re: Time stamp transformation
            Agnivesh Kumar

            Please provide some sample data .

            • Re: Time stamp transformation
              Marco Wedel

              Hi,

               

              another solution could be:

               

              QlikCommunity_Thread_198109_Pic1.JPG

               

              table1:
              LOAD RecNo() as key, *
              Inline [
              ID, Weekday, SiteOpeningTime, SiteClosingTime
              1, Monday,    00:00:00, 23:59:00
              1, Tuesday,  02:10:00, 23:30:00
              1, Wednesday, 03:20:00, 22:00:00
              1, Thursday,  04:30:00, 21:40:00
              1, Friday,    07:40:00, 20:20:00
              1, Saturday,  12:50:00, 19:30:00
              1, Sunday,    15:00:00, 18:10:00
              2, Monday,    00:00:00, 23:59:00
              2, Tuesday,  01:20:00, 22:49:00
              2, Wednesday, 02:30:00, 21:39:00
              2, Thursday,  03:40:00, 20:29:00
              2, Friday,    04:50:00, 19:19:00
              2, Saturday,  06:00:00, 18:09:00
              2, Sunday,    07:10:00, 17:00:00
              ];
              
              table2:
              LOAD key,
                  Hour,
                  Interval#(Interval(RangeMax(RangeMin(SiteClosingTime,Hour+'01:00')-RangeMax(SiteOpeningTime,Hour),0),'hh:mm'),'hh:mm') as TimeOpen;
              LOAD key,
                  Time((IterNo()-1)*'01:00','hh:mm') as Hour,
                  SiteOpeningTime,
                  SiteClosingTime
              Resident table1
              While IterNo()<=24;
              

               

               

              hope this helps

               

              regards

               

              Marco

              • Re: Time stamp transformation
                marcin szuberla

                This is great! Thank you!