Hi Bill,
Code is;
Page1:
/* Setup for incremental load. */
/*
The following items are used multiple times in the script.
It's easiest to specify them in variables.
*/
Let vPath = '\\Qlikview01\Qlikview\';
Let vToday = Date(Today(2));
SET vQvdFile ='$(vPath)Data\QVDs\MCC\MCC-Inbound-TR.qvd'; //?? The QVD filename
SET vTableName='Inbound'; //?? The name of the QV table we are loading on the Data Load tab
SET vPK='InboundCallTrackingTblID'; //?? Data Primary Key
/*
Update the QVD with changes.
*/
Page2:
// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False
LET vQvdExists = if(Filesize('$(vQvdFile)') > 0, -1, 0);
IF $(vQvdExists) THEN // QVD exists, we will do an incremental reload
Inbound:
LOAD InboundCallTrackingTblID,
CallID,
CalledNumber,
CallerNumber,
AgentID,
Action,
Answered,
AnsweredNoVoicemail,
Missed,
InternalOverFlowTimeOut,
InternalOverFlowToLongWaiting,
InternalOverFlowToManyWaiting,
InternalOverFlowCalendar,
InternalOverFlowHoliday,
InternalOverFlowManual,
InternalOverFlowNoAgent,
ExternalOverFlowTimeOut,
ExternalOverFlowToLongWaiting,
ExternalOverFlowToManyWaiting,
ExternalOverFlowCalendar,
ExternalOverFlowHoliday,
ExternalOverFlowManual,
ExternalOverFlowNoAgent,
StartTime,
Date(CallDate) as CallDate,
CallHour,
CallMinute,
CallTime,
CallTableDate,
MinutesThirty,
MinutesFifteen,
EndTime,
Duration,
WaitDuration,
HoldTimeDuration,
SkillID,
CallerTitle,
CallerName,
CallerSureName,
CallerCompany,
BrandExAdmin,
CallerStreet,
CallerZIP,
CallerCity,
CallerCountry,
CallerPhone,
VIPClassID,
PhoneClassID,
connected,
TransferID1,
TransferID2,
TransferID3,
TransferID4,
PhoneCallStart,
Classification,
PickAgentName,
PickMode,
'Old' as Check
FROM
$(vQvdFile)
(qvd)
Where StartTime > Today(2)-730
;
// Get the max date from this QVD for use in incremental SELECT
LOAD date(max(StartTime),'YYYY-MM-DD hh:mm:ss[.fff]') as maxdate Resident Inbound;
//?? Set the name of the date or datetime field
//?? Date may need to be formatted to match the format expected by your SQL SELECT or LOAD statement.
//?? e.g., date(peek('maxdate'),'MMDDYYYY');
LET vIncrementalExpression ='WHERE StartTime >= ' & chr(39) & peek('maxdate') & chr(39);
Drop Field maxdate;
ELSE // QVD does not exist
LET vIncrementalExpression = ''; // No QVD. Force full reload
END IF
Page 3:
LOAD
ID AS InboundCallTrackingTblID,
CallID,
CalledNumber,
CallerNumber,
AgentID,
Action,
//Wait Time Variables
If(Action = 'Connected' OR Action = 'Voicemail' OR Action = 'TransferExternal' OR Action = 'TransferInternal',1,0) AS Answered,
If(Action = 'Connected' OR Action = 'TransferExternal' OR Action = 'TransferInternal',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,
Date(StartTime, 'DD/MM/YYYY') AS CallTableDate,
//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,
//Hold Time Duration Variable
if(Action='OnHold',Duration,Null()) AS HoldTimeDuration,
SkillID,
CallerTitle,
CallerName,
CallerSureName,
CallerCompany,
// If(CallerCompany='Fortoak' OR CallerCompany='Yes' OR CallerCompany='Church Supplies' OR PhoneClass='7' OR PhoneClass='8' OR PhoneClass='9' OR PhoneClass='10' OR PhoneClass='11' OR PhoneClass='12', 1, 0) AS BrandExAdmin,
If(PhoneClass='7' OR PhoneClass='8' OR PhoneClass='9' OR PhoneClass='10' OR PhoneClass='11' OR PhoneClass='12' OR PhoneClass='15' OR PhoneClass='16' OR PhoneClass='17' OR PhoneClass='18', 1, 0) AS BrandExAdmin,
CallerStreet,
CallerZIP,
CallerCity,
CallerCountry,
CallerPhone,
VIPClass AS VIPClassID,
PhoneClass AS PhoneClassID,
connected,
TransferID1,
TransferID2,
TransferID3,
TransferID4,
PhoneCallStart,
Classification,
AgentName AS PickAgentName,
PickMode,
'New' as Check
;
SQL SELECT *
FROM CCDB_02.dbo.InboundCallTracking Order by StartTime ASC
$(vIncrementalExpression)
;
Page4:
QUALIFY * ;
SaveFile:
Load *
Resident Inbound
Where Date(CallTableDate) < '$(vToday)'
;
UnQUALIFY * ;
EXIT Script;
Store SaveFile INTO $(vQvdFile);
Drop Table SaveFile;
Exit Script;
END
What I'm trying to do is load everything except the current day's data so that i can archive it off. The SQL server will be offsite so i want the majority of the Data to be held locally in a QVD and then we simply are doing a SQL connect for the latest days data. The Report needs to reload every 30 minutes so i want the external SQL connection to get just today's records.
Hope that makes sense.
Nick
