6 Replies Latest reply: Feb 23, 2016 5:48 AM by John Pillinger RSS

    Help getting going with Dates/Times/TimeBuckets

    John Pillinger

      Hi,

       

      Newbie question I'm afraid (yes, I'll search the community, but was hoping someone might take pity)

       

      I have a file with a bunch of dates in this format;

       

      December 1 2015 08:56 PM

       

      I want to;

       

      Add counts on hourly timebuckets

      Add counts on days of the week,  Mon,Tues etc

      Add counts on days of the month 1,2,3

       

      Any help appreciated;

       

      Thanks for looking.

        • Re: Help getting going with Dates/Times/TimeBuckets
          Oleg Troyansky

          Hi,

           

          it looks like your dates are stored as strings and not timestamps. An easy way to tell is to preview the data and see if the values appear right-justified (numbers and timestamps) or left-justified (strings).

           

          In case your dates are strings, you need to convert the string to the proper timestamp data type. Something like this should work:

           

          timestamp#(MyDate, 'MMMM D YYYY hh:mm TT')

           

          Then, you might want to format the result using a new format pattern:

           

          timestamp(timestamp#(MyDate, 'MMMM D YYYY hh:mm TT'), 'new format patten')

           

          Once this is done, you can use QlikView Date functions to calculate Year, Month, WeekDay, Hour, and whatever else you need.

           

          If you'd like to learn QlikView development techniques methodically, starting from the basics and going into the most advanced topics, check out my new book QlikView Your Business.

           

          cheers,

          Oleg Troyansky

          Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

          • Re: Help getting going with Dates/Times/TimeBuckets
            Stefan Wühl

            1) read your timestamps in, set the format code correctly or use Timestamp#() to interprete the timestamps

            2) Create additional fields using date and time functions

            3) create a chart with one of the new fields as dimension and count(FIELD) as expression

             

             

            SET TimestampFormat = 'MMMM D YYYY hh:mm TT';

             

            LOAD TimestampField,

                       Hour(TimestampField) as Hour,

                      Weekday(TimestampField) as WeekDay,

                      Day(TimestampField) as Day

            FROM ...;

             

             

            Now create e.g. a chart with dimension Hour and as expression

            =Count(Hour)

            • Re: Help getting going with Dates/Times/TimeBuckets
              Massimo Grossi

              if your field is dt

               

              Set LongMonthNames =        'January;February;March;April;May;June;July;August;September;October;November;December';


              load

                dt, t,

                WeekDay(t) as weekday,

                Day(t) as day,

                floor(frac(t),1/24)*24 as hour;

              load   dt,  timestamp(Timestamp#(dt, 'MMMM D YYYY hh:mm tt')) as t;

              load * inline [

              dt

              December 1 2015 08:56 PM

              December 1 2015 08:01 PM

              December 1 2015 08:01 AM

              December 1 2015 08:56 AM

              December 1 2015 12:01 AM

              ];

               

               

              1.png

              • Re: Help getting going with Dates/Times/TimeBuckets
                John Pillinger

                Thanks guys for taking the time to reply. I think you all contributed to me getting the right answer.

                 

                SET TimestampFormat = 'MMMM D YYYY hh:mm TT';

                 

                LOAD timestamp#(@1, 'MMMM D YYYY hh:mm TT'),

                           Hour(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Hour,

                          Weekday(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as WeekDay,

                          Day(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Day,

                          Month(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Month,

                          Week(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Week,

                          Year(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Year

                 

                FROM [lib://qlikid_jpqlikqlik/demo.csv]

                (txt, codepage is 1252, no labels, delimiter is ',', msq);

                 

                It works really well.

                 

                Next step is for me to work out how to have year+month, so Dec15 can be separated form Dec16.

                 

                I'll keep playing.

                 

                Thanks again.