Peek Function / Append Load

Mon, 30 Nov 2009 20:57:26 GMT

Hi all,

I am creating a Append Load for my data using QVDs to speed up the reload time of my document.

I have successfully created the scrip which creates a QVD of the data and then concatenates any "new" data from the database onto the bottom of the QVD.

The problem I am having is with the Peek ( ) function. I am trying to select the last record in the QVD and store the ID into a variable which i will then use later to restrict the re-load from the database. (load everything after this ID)

Although where I am setting the variable, called "successful", equal to Peek(InboundCallTblID, -1 ,Inbound) using "-1" to select the last record of the QVD, the last record ID is not selected? I beleive it is selecting the first record ID in fact as if I remove the minus sign it returns the same ID?

Here is the code I have created:

/* InboundCallTracking */

SET QVDFILE = E:\QlikView\QlikView Data\QlikView Reports\MCC\QVDs\INBOUND.qvd;

IF FileSize( '$(QVDFILE)' ) > 0 THEN

SET QVD_Exists = 1;

ELSE

SET QVD_Exists = 0;

END IF

IF $(QVD_Exists) THEN

InboundQVD:

LOAD * FROM $(QVDFILE) (qvd);

LET Successful = PEEK('InboundCallTrackingTblID',-1,'InboundQVD');

ELSE

LET Successful = 0;

END IF

DROP table InboundQVD;

IF $(Successful) > 0 THEN

Inbound:

LOAD

ID AS InboundCallTrackingTblID,

CallID,

CalledNumber,

CallerNumber,

AgentID,

Action,

//Wait Time Variables

If(Action = 'Connected' OR Action = 'Voicemail' OR Action = 'TransferInternal' OR Action = 'TransferExternal',1,0) AS Answered,

If(Action = 'Connected' OR Action = 'TransferExternal',1,0) AS AnsweredNoVoicemail,

If(Action <> 'Connected' AND Action <> 'TransferExternal' AND Action <> 'Voicemail' AND Action <> 'TransferInternal',1,0) AS Missed,

//Internal Overflow Variables

If(Action = 'TransferInternal' OR Action = 'TimeOut',1,0) AS InternalOverFlowTimeOut,

If(Action = 'TransferInternal' OR Action = 'ToLongWaiting',1,0) AS InternalOverFlowToLongWaiting,

If(Action = 'TransferInternal' OR Action = 'ToManyWaiting',1,0) AS InternalOverFlowToManyWaiting,

If(Action = 'TransferInternal' OR Action = 'CalendarClosed',1,0) AS InternalOverFlowCalendar,

If(Action = 'TransferInternal' OR Action = 'HolidayClosed',1,0) AS InternalOverFlowHoliday,

If(Action = 'TransferInternal' OR Action = 'ManualClosed',1,0) AS InternalOverFlowManual,

If(Action = 'TransferInternal' OR Action = 'NoAgent',1,0) AS InternalOverFlowNoAgent,

//External Overflow Variables

If(Action = 'TransferExternal' OR Action = 'TimeOut',1,0) AS ExternalOverFlowTimeOut,

If(Action = 'TransferExternal' OR Action = 'ToLongWaiting',1,0) AS ExternalOverFlowToLongWaiting,

If(Action = 'TransferExternal' OR Action = 'ToManyWaiting',1,0) AS ExternalOverFlowToManyWaiting,

If(Action = 'TransferExternal' OR Action = 'CalendarClosed',1,0) AS ExternalOverFlowCalendar,

If(Action = 'TransferExternal' OR Action = 'HolidayClosed',1,0) AS ExternalOverFlowHoliday,

If(Action = 'TransferExternal' OR Action = 'ManualClosed',1,0) AS ExternalOverFlowManual,

If(Action = 'TransferExternal' OR Action = 'NoAgent',1,0) AS ExternalOverFlowNoAgent,

StartTime,

Floor(StartTime) AS CallDate,

Hour(StartTime) AS CallHour,

Minute(StartTime) AS CallMinute,

Time(StartTime) AS CallTime,

//Interval Setup

if ( minute(StartTime) < 30, '00', '30') AS MinutesThirty,

if ( minute(StartTime) < 15, '00', if( minute(StartTime) < 30, '15', if( minute(StartTime) < 45, '30', '45' ))) AS MinutesFifteen,

EndTime,

Duration,

//Wait Durration Variable

if(Action='Waiting',Duration,Null()) AS WaitDuration,

SkillID,

CallerTitle,

CallerName,

CallerSureName,

CallerCompany,

CallerStreet,

CallerZIP,

CallerCity,

CallerCountry,

CallerPhone,

VIPClass AS VIPClassID,

PhoneClass AS PhoneClassID,

connected,

TransferID1,

TransferID2,

TransferID3,

TransferID4,

PhoneCallStart,

Classification,

PickedFromAgent;

SQL SELECT *

FROM CCDB.dbo.InboundCallTracking WHERE ID > $(Successful);

END IF

CONCATENATE LOAD * FROM $(QVDFILE) (qvd);

STORE Inbound INTO E:\QlikView\QlikView Data\QlikView Reports\MCC\QVDs\INBOUND.qvd;

Can anyone help here or no any reason why I mite not be returning the last record ID?

Thanks,

Joe

Re: Peek Function / Append Load

Wed, 02 Nov 2011 14:09:38 GMT

I would suspect that the peek function no longer has a 'last record read' associated with it because you have finished your load statement.

If your record IDs are sequential, (as suggested by the fact that you can use 'Successful' as the start point of your select statement) then I would suggest either:

if starting from 1:

LET Successful = QvdNoOfRecords( '$(QVDFILE)' )

if starting from some arbitrary number and you haven't got the field loaded with more values elsewhere previously:

LET Successful = max( InboundCallTrackingTblID)

(Note: neither code has been tested as I have nothing eqivalent to test against)

EDIT: as an afterthought, it might be sufficient to move your peek to above your load statement (load on load logic)?

topic Peek Function / Append Load in QlikView

Peek Function / Append Load

Re: Peek Function / Append Load