Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)' )
or
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)?