Problem with ApplyMap and dates

Mon, 16 Mar 2015 12:02:44 GMT

Hi!

I'm having trouble getting ApplyMap to work with dates (stored as TIMESTAMP in Oracle). I have built a calendar as follows:

TempCalendar:
  LOAD
  Date($(vMinInst) + RowNo() - 1) as TempDate
  AutoGenerate $(vMaxInst) - $(vMinInst) + 1;


Calendar:
  LOAD
    TempDate              as TimeId
  , Day(TempDate)        as Day
  , DayName(TempDate)    as DayDesc
  , Week(TempDate)        as Week
  , WeekName(TempDate)    as WeekDesc
  , Month(TempDate)      as Month
  , Year(TempDate)        as Year
  , QuarterName(TempDate) as Quarter
  Resident TempCalendar;

I then build a mapping table based on Calendar:

Map_Time:
  Mapping
  LOAD
  TimeId, AutoNumber(TimeId) as AutoTimeId
  Resident Calendar;

However, when I do the following, I get NONE instead of the auto-numbered value I expect:

ApplyMap('Map_Time',Date(SOME_TIMESTAMP_FROM_ORACLE),'NONE')

The ApplyMap function is applied to a table that is already in QlikView, so the issue is not that Date is unknown in Oracle; this would cause Oracle to spit out an error anyway. The table is loaded with a resident load after I have imported the relevant data from the DB:

TempData:
  SELECT * FROM some_table;

MyTab:
  LOAD
     ApplyMap(...)
 , ...
  Resident TempData;

What am I doing wrong?

I am using a similar technique with a mapping table and ApplyMap on other fields and it works like a charm. However, with the date field from the database the mapping table seems to yield nothing. Removing AutoNumber yields the same (disappointing) result.

Any help would be greatly appreciated.

Message was edited by: Christian Hellström - added clarification on load.

Re: Problem with ApplyMap and dates

Mon, 16 Mar 2015 14:09:47 GMT

I know it's bad to answer your own questions but it seems I have figured it out: Date is a formatting function that causes different times during the same day to be displayed the same but stored differently (numerically). So, when QlikView looks up the (numerical) value it finds no match because the mapping table has other entries after the decimal point.

The solution is to use Date(DayStart(...)) instead of just Date(...). This way, the output and the storage are exactly the same. Date strips off the time part, which is what I'm not interested in anyway.

Silly me.

topic Problem with ApplyMap and dates in QlikView

Problem with ApplyMap and dates

Re: Problem with ApplyMap and dates