topic Applymap on time field in QlikView

Applymap on time field

Wed, 21 Sep 2011 08:05:35 GMT

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

s_uhlig — Wed, 21 Sep 2011 08:16:30 GMT

Hi,

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

Regards

Sven

Applymap on time field

SunilChauhan — Wed, 21 Sep 2011 08:33:33 GMT

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

(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

Wed, 21 Sep 2011 12:26:31 GMT

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