3 Replies Latest reply: Sep 21, 2011 8:26 AM by Interserve RSS

    Applymap on time field

      Hi,

       

      i have a problem which I hope you guys can sort out.  I simply have  time field that I need to map to some data which corrosponds night and daytime readings so there are ultamtley 2 tables one with the time and if its day or night and the other which is my electrical readings.

       

      The problem I see is that the time field appears to be text in one table (as its a crosstab) compared to the inline table which is correctly showing it as a time.

       

      Can anyone help?

        • Applymap on time field
          Sven Uhlig

          Hi,

           

          first of all you should use "mapping load" to define a mapping table.

           

          Regards

          Sven

            • Applymap on time field
              Sunil Chauhan

              try this code

               

               

              ElectricReadings_tmp:

              CrossTable(Time , Data)
              LOAD CalendarDate,
                   [0:00],
                   [0:30],
                   [1:00],
                   [1:30],
                   [2:00],
                   [2:30],
                   [3:00],
                   [3:30],
                   [4:00],
                   [4:30],
                   [5:00],
                   [5:30],
                   [6:00],
                   [6:30],
                   [7:00],
                   [7:30],
                   [8:00],
                   [8:30],
                   [9:00],
                   [9:30],
                   [10:00],
                   [10:30],
                   [11:00],
                   [11:30],
                   [12:00],
                   [12:30],
                   [13:00],
                   [13:30],
                   [14:00],
                   [14:30],
                   [15:00],
                   [15:30],
                   [16:00],
                   [16:30],
                   [17:00],
                   [17:30],
                   [18:00],
                   [18:30],
                   [19:00],
                   [19:30],
                   [20:00],
                   [20:30],
                   [21:00],
                   [21:30],
                   [22:00],
                   [22:30],
                   [23:00],
                   [23:30]
              FROM
              [C:\Qlikview\BI\External Files\Excel\BAMC\Electric Readings.xls]
              (biff, embedded labels, table is Master$)
              where CalendarDate >0;

              TimeMap:
              mapping
              load * inline [
              Time,Type
                   0:00, Night
                   0:30, Night
                   1:00, Night
                   1:30, Night
                   2:00, Night
                   2:30, Night
                   3:00, Night
                   3:30, Night
                   4:00, Night
                   4:30, Night
                   5:00, Night
                   5:30, Night
                   6:00, Day
                   6:30, Day
                   7:00, Day
                   7:30, Day
                   8:00, Day
                   8:30, Day
                   9:00, Day
                   9:30, Day
                   10:00, Day
                   10:30, Day
                   11:00, Day
                   11:30, Day
                   12:00, Day
                   12:30, Day
                   13:00, Day
                   13:30, Day
                   14:00, Day
                   14:30, Day
                   15:00, Day
                   15:30, Day
                   16:00, Day
                   16:30, Day
                   17:00, Day
                   17:30, Day
                   18:00, Night
                   18:30, Night
                   19:00, Night
                   19:30, Night
                   20:00, Night
                   20:30, Night
                   21:00, Night
                   21:30, Night
                   22:00, Night
                   22:30, Night
                   23:00, Night
                   23:30, Night];

              ElectricReadings:
              Load
              Time( Time) as Time1,
              Data,
              CalendarDate,
              applymap('TimeMap',Time,1000) as NightOrDay

               

              resident ElectricReadings_tmp;

            • Applymap on time field

              Thanks guys!

               

              I found the problem, this was due to the data types not maching i had to do the following to fix (based on code above).  the bold portion was what was missing.

               

              ElectricReadings:

              Load

              Time ,

              Data,

              CalendarDate,

              applymap ('TimeMap',text(Time)) as NightOrDay