topic Applymap with time range in QlikView

Applymap with time range

sifatnabil — Thu, 23 Oct 2014 01:27:29 GMT

Hi,

I have data from an Oracle database, where there is a field called MSG_DATE_TIME which is of data type = "timestamp (6) with local time zone".

When the main table updates with a new row, the mapping table updates immediately after, with a few milliseconds delay, like 5 milliseconds. I want to map the FIXING_STATE, so the mapping load would be like this:

mapstate:

MAPPING LOAD

MSG_DATE_TIME & ' ' & DESCRIPTION,

FIXING_STATE

Resident mappingtable;

The problem is that the MSG_DATE_TIME in the main table and mapping table lag by 5 milliseconds. How can the mapping be done such that the applymap statement can accommodate the 5 millisecond lag time?

The logic would be something like this:

applymap('mapstate',MSG_DATE_TIME within 5 milliseconds & ' ' & DESCRIPTION)

Thanks.

Re: Applymap with time range

MarcoWedel — Fri, 24 Oct 2014 21:56:09 GMT

maybe you could use intervalmatch instead of mapping load / applymap.

Re: Applymap with time range

kuba_michalik — Fri, 24 Oct 2014 22:22:41 GMT

If the corresponding updates to both tables maintain their relative order, you could sequentially number records in each table ordered by timestamp and use that instead of timestamps for matching records. Using ranges would make me nervous about overlaps, althouth that depends on frequency of updates vs lag period.

Re: Applymap with time range

Sat, 25 Oct 2014 14:08:57 GMT

Rahman, May be you can remove the milliseconds from both dates and do apply map if possible.

Otherwise, use the IntervelMatch instead of applymap.