4 Replies Latest reply: Dec 13, 2012 6:52 AM by dasbmx32 RSS

    Create day and hour field

      For my report I need to look at hours within a day. I want to create a field that has mon12:00, mon13:00, mon14:00 etc. for a whole week. Now I included this in my script:

       

      Weekday(date)&' '&hour&':00' as DayHour,

       

      This gives me what I need, only it sorts in the wrong way. The variable "hour" is loaded from database as an integer with values from 0 to 23. Now this needs to be formatted or primed in some way so that Qlikview sees it as hours in a day with sort order 0 - 23. Also the weekday is only sorting aphabetically.

      How can this field be created so that it sorts properly?

        • Re: Create day and hour field
          Gysbert Wassenaar

          Try num(hour,'00') so the hour always has two digits

           

          Days:

          load * inline [

          day

          Mon

          Tue

          Wed

          Thu

          Fri

          Sat

          Sun];

           

          join

          Hours:

          load num(rowno()-1,'00') &':00' as hour

          AutoGenerate 24;

           

          Result:

          load day&hour as dayhour

          Resident Days;

           

          drop table Days;

            • Re: Create day and hour field

              Hi Gysbert, thanks for the input! I tried it in my script, but don't get the right output. I deleted "Hours:" which blocked the script. Autogenerate has an issue with the 24, does that need brackets?

              I don't see how this script ties into the field "hour" which is in my original database, in this script you are creating a new field called "hour" from scratch. Also, why are the day names loaded by inline load, they are already specified by the set statement and the date field in the script?

              Can you explain what your script tries to do, I am rather new to the scripting so it is hard for me to understand what is going on.

              Thanks for any further help, it is much appreciated!

                • Re: Create day and hour field
                  Gysbert Wassenaar

                  Just try: Weekday(date)&' '&num(hour,'00')&':00' as DayHour,

                   

                  num(hour,'00') formats the single digit integer as a two digit number.

                   

                  My script is just an example that generates 24 hours (00 to 23) and then outer joins it with a table of days. The set variable merely specifies which values should be used for day names. It's not a table.