Qlikview增量加载数据库里的数据到QVD文件。
MainTab:
LET LastReload=reloadtime();
LET Curr=now();
TRACE <<<<< Current Time is $(Curr), Last Reload Time is $(LastReload)>>>>>;
SET ThousandSep=',';MainTab:
LET LastReload=reloadtime();
LET Curr=now();
TRACE <<<<< Current Time is $(Curr), Last Reload Time is $(LastReload)>>>>>;
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//setvQvdFolder='D:ManieABCQV_REPORTPLSQVD';
set vQvdFolder='D:WatsonABCQV_REPORTBDSQVD'; //The location is on Watson's own PC
SET vDBName='DW';
//OLEDB CONNECT TO[Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;DataSource=192.168.2.250;Use Procedure for Prepare=1;Auto Translate=True;PacketSize=4096;Workstation ID=8BFWC02;Use Encryption for Data=False;Tag with columncollation when possible=False];
//ODBC CONNECT32 TO DW;
//OLEDB CONNECT32 TO[Provider=SQLOLEDB.1;Persist Security Info=False;User ID=vscmis;InitialCatalog=DW;Data Source=192.168.2.250;Use Procedure for Prepare=1;AutoTranslate=True;Packet Size=4096;Workstation ID=R9HDET7;Use Encryption forData=False;Tag with column collation when possible=False];
OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=False;UserID=vscmis;Initial Catalog=DW;Data Source=192.168.2.250;Use Procedure forPrepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=R9HDET7;UseEncryption for Data=False;Tag with column collation when possible=False];
IF Month(now())>=4 THEN
LET vStartYear=Year(now())-2;
ELSE
LET vStartYear=Year(now())-3;
ENDIF;
SET vMonthDay='04-01';
LET vStartDate=Date('$(vStartYear)-$(vMonthDay)','YYYY-MM-DD');//num(Date('$(vStartYear)-$(vMonthDay)','YYYY-MM-DD'));
LET vEndDate=Date(today()-1,'YYYY-MM-DD');//num(today()-1);num(Date('2012-04-18','YYYY-MM-DD'));
Trace <<<<<Period: From $(vStartDate) to $(vEndDate) >>>>>;
Transaction Tab:
MeasureTable:
LOAD
* INLINE
[Measure
ABC_BI_INV_LINES
ABC_BI_INV_HEADERS
];
FOR counter = 1 to NoOfRows('MeasureTable') //FORcounter = 1 to NoOfRows('SourceTable')
LET vSourceName=Fieldvalue('Measure', $(counter));
IF '$(vSourceName)'='ABC_BI_INV_HEADERS' THEN
SET DateField='INVOICE_DATE';
ELSE
SET DateField='TRX_DATE';
ENDIF;
For Date = num('$(vStartDate)') to num('$(vEndDate)')
LET vDate=Date($(Date),'YYYY-MM-DD');
TRACE <<<<< DATE COLUMN=$(DateField) ----Split: Counter=$(counter) Measure=$(vSourceName) Date=$(vDate),$(Date) >>>>>;
$(vSourceName):
LOAD *;
SQL SELECT *
FROM "$(vDBName)".dbo."$(vSourceName)"
WHERE $(DateField)='$(vDate)' ;
Let nRow=NoOfRows('$(vSourceName)');
IF NoOfRows('$(vSourceName)')>0 THEN
TRACE <<<<< nRows=$(nRow)>>>>>;
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vDate).qvd(qvd);
ENDIF;
DROP TABLE $(vSourceName);
NEXT Date
NEXT counter
DROP TABLEMeasureTable;
Partial Transaction Tab:
/* Reload for today's data orfor a specific day */
Trace <<<<Reload Daily ABC_BI_INV_LINES/ABC_BI_INV_HEADERS From SQLServer to QVD>>>>;
LET vReloadDate=date(today()-1,'YYYY-MM-DD');
//LET vReloadDate='2012-06-01';
MeasureTable:
Load
* INLINE
[Measure
ABC_BI_INV_LINES
ABC_BI_INV_HEADERS
];
FOR counter = 1 to NoOfRows('MeasureTable')
LET vSourceName=Fieldvalue('Measure', $(counter));
IF '$(vSourceName)'='ABC_BI_INV_HEADERS' THEN
SET DateField='INVOICE_DATE';
ELSE
SET DateField='TRX_DATE';
ENDIF;
TRACE <<<<< Reload: Counter=$(counter) Measure=$(vSourceName) Reload Date=$(vReloadDate)>>>>>;
$(vSourceName):
LOAD *;
SQL SELECT *
FROM "$(vDBName)".dbo."$(vSourceName)"
WHERE $(DateField)='$(vReloadDate)';
Let nRow=NoOfRows('$(vSourceName)');
IF NoOfRows('$(vSourceName)')>0 THEN
TRACE <<<<< nRows=$(nRow)>>>>>;
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vReloadDate).qvd(qvd);
ENDIF;
DROP TABLE $(vSourceName);
NEXT counter;
DROP TABLEMeasureTable;
Merge Daily QVD Tab:
/* Merge 3-Year daily QVDs into1 3-year QVD */
Trace <<<<< Merge Daily QVD >>>>>;
MeasureTable:
Load
* INLINE
[Measure
ABC_BI_INV_LINES
ABC_BI_INV_HEADERS
];
FOR counter = 1 to NoOfRows('MeasureTable')
LET vSourceName=Fieldvalue('Measure', $(counter));
TRACE <<<<< Merge: Counter=$(counter) Measure=$(vSourceName) >>>>>;
Let iFile=0;
For Each File in filelist(vQvdFolder& '$(vSourceName)$(vSourceName)_*.qvd')
LET vFileDate=Left(right('$(File)',14),10);
TRACE <<<<< FileDate=$(vFileDate) >>>>>;
IF num('$(vFileDate)')>=num('$(vStartDate)') AND num('$(vFileDate)')<=num('$(vEndDate)') THEN
IF $(iFile)=0 THEN
TRACE <<<Replace existing QVD, iFile=0>>>;
$(vSourceName):
LOAD *
FROM $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vFileDate).qvd(qvd);
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName).qvd(qvd);
ENDIF;
IF $(iFile)=1 THEN
TRACE <<<Update existing QVD, iFile=$(iFile)>>>;
$(vSourceName):
LOAD *
FROM $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vFileDate).qvd(qvd);
Join ($(vSourceName))
LOAD *
FROM $(vQvdFolder)$(vSourceName).qvd(qvd);
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName).qvd(qvd);
ENDIF;
LET iFile=1;
DROP TABLE $(vSourceName);
ENDIF;
NEXT File
Next counter
DROP TABLEMeasureTable;
Update Dimension Tab:
TRACE <<<<<Update Dimension Tables >>>>>;
DimTable:
Load
* INLINE
[Dimension
ABC_BI_ITEM
ABC_BI_SALESREPS
];
For counter =1 to NoOfRows('DimTable')
LET vSourceName=Fieldvalue('Dimension',$(counter));
Trace <<<<<Counter=$(counter)Update Table $(vSourceName)>>>>>;
$(vSourceName):
LOAD *;
SQL SELECT *
FROM "$(vDBName)".dbo."$(vSourceName)";
Let nRow=NoOfRows('$(vSourceName)');
IF NoOfRows('$(vSourceName)')>0THEN
TRACE<<<<< nRows=$(nRow)>>>>>;
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName).qvd(qvd);
ENDIF;
DROP TABLE $(vSourceName);
TRACE<<<<<DIMENSION TABLE UPDATED>>>>>;
Next counter
DROP TABLE DimTable;
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//setvQvdFolder='D:ManieABCQV_REPORTPLSQVD';
set vQvdFolder='D:WatsonABCQV_REPORTBDSQVD'; //The location is on Watson's own PC
SET vDBName='DW';
//OLEDB CONNECT TO[Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;DataSource=192.168.2.250;Use Procedure for Prepare=1;Auto Translate=True;PacketSize=4096;Workstation ID=8BFWC02;Use Encryption for Data=False;Tag with columncollation when possible=False];
//ODBC CONNECT32 TO DW;
//OLEDB CONNECT32 TO[Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abcmis;InitialCatalog=DW;Data Source=192.168.2.250;Use Procedure for Prepare=1;AutoTranslate=True;Packet Size=4096;Workstation ID=R9HDET7;Use Encryption forData=False;Tag with column collation when possible=False];
OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=False;UserID=abcmis;Initial Catalog=DW;Data Source=192.168.2.250;Use Procedure forPrepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=R9HDET7;UseEncryption for Data=False;Tag with column collation when possible=False];
IF Month(now())>=4 THEN
LET vStartYear=Year(now())-2;
ELSE
LET vStartYear=Year(now())-3;
ENDIF;
SET vMonthDay='04-01';
LET vStartDate=Date('$(vStartYear)-$(vMonthDay)','YYYY-MM-DD');//num(Date('$(vStartYear)-$(vMonthDay)','YYYY-MM-DD'));
LET vEndDate=Date(today()-1,'YYYY-MM-DD');//num(today()-1);num(Date('2012-04-18','YYYY-MM-DD'));
Trace <<<<<Period: From $(vStartDate) to $(vEndDate) >>>>>;
Transaction Tab:
MeasureTable:
LOAD
* INLINE
[Measure
ABC_BI_INV_LINES
ABC_BI_INV_HEADERS
];
FOR counter = 1 to NoOfRows('MeasureTable') //FORcounter = 1 to NoOfRows('SourceTable')
LET vSourceName=Fieldvalue('Measure', $(counter));
IF '$(vSourceName)'='ABC_BI_INV_HEADERS' THEN
SET DateField='INVOICE_DATE';
ELSE
SET DateField='TRX_DATE';
ENDIF;
For Date = num('$(vStartDate)') to num('$(vEndDate)')
LET vDate=Date($(Date),'YYYY-MM-DD');
TRACE <<<<< DATE COLUMN=$(DateField) ----Split: Counter=$(counter) Measure=$(vSourceName) Date=$(vDate),$(Date) >>>>>;
$(vSourceName):
LOAD *;
SQL SELECT *
FROM "$(vDBName)".dbo."$(vSourceName)"
WHERE $(DateField)='$(vDate)' ;
Let nRow=NoOfRows('$(vSourceName)');
IF NoOfRows('$(vSourceName)')>0 THEN
TRACE <<<<< nRows=$(nRow)>>>>>;
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vDate).qvd(qvd);
ENDIF;
DROP TABLE $(vSourceName);
NEXT Date
NEXT counter
DROP TABLEMeasureTable;
Partial Transaction Tab:
/* Reload for today's data orfor a specific day */
Trace <<<<Reload Daily ABC_BI_INV_LINES/ABC_BI_INV_HEADERS From SQLServer to QVD>>>>;
LET vReloadDate=date(today()-1,'YYYY-MM-DD');
//LET vReloadDate='2012-06-01';
MeasureTable:
Load
* INLINE
[Measure
ABC_BI_INV_LINES
ABC_BI_INV_HEADERS
];
FOR counter = 1 to NoOfRows('MeasureTable')
LET vSourceName=Fieldvalue('Measure', $(counter));
IF '$(vSourceName)'='ABC_BI_INV_HEADERS' THEN
SET DateField='INVOICE_DATE';
ELSE
SET DateField='TRX_DATE';
ENDIF;
TRACE <<<<< Reload: Counter=$(counter) Measure=$(vSourceName) Reload Date=$(vReloadDate)>>>>>;
$(vSourceName):
LOAD *;
SQL SELECT *
FROM "$(vDBName)".dbo."$(vSourceName)"
WHERE $(DateField)='$(vReloadDate)';
Let nRow=NoOfRows('$(vSourceName)');
IF NoOfRows('$(vSourceName)')>0 THEN
TRACE <<<<< nRows=$(nRow)>>>>>;
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vReloadDate).qvd(qvd);
ENDIF;
DROP TABLE $(vSourceName);
NEXT counter;
DROP TABLEMeasureTable;
Merge Daily QVD Tab:
/* Merge 3-Year daily QVDs into1 3-year QVD */
Trace <<<<< Merge Daily QVD >>>>>;
MeasureTable:
Load
* INLINE
[Measure
ABC_BI_INV_LINES
ABC_BI_INV_HEADERS
];
FOR counter = 1 to NoOfRows('MeasureTable')
LET vSourceName=Fieldvalue('Measure', $(counter));
TRACE <<<<< Merge: Counter=$(counter) Measure=$(vSourceName) >>>>>;
Let iFile=0;
For Each File in filelist(vQvdFolder& '$(vSourceName)$(vSourceName)_*.qvd')
LET vFileDate=Left(right('$(File)',14),10);
TRACE <<<<< FileDate=$(vFileDate) >>>>>;
IF num('$(vFileDate)')>=num('$(vStartDate)') AND num('$(vFileDate)')<=num('$(vEndDate)') THEN
IF $(iFile)=0 THEN
TRACE <<<Replace existing QVD, iFile=0>>>;
$(vSourceName):
LOAD *
FROM $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vFileDate).qvd(qvd);
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName).qvd(qvd);
ENDIF;
IF $(iFile)=1 THEN
TRACE <<<Update existing QVD, iFile=$(iFile)>>>;
$(vSourceName):
LOAD *
FROM $(vQvdFolder)$(vSourceName)$(vSourceName)_$(vFileDate).qvd(qvd);
Join ($(vSourceName))
LOAD *
FROM $(vQvdFolder)$(vSourceName).qvd(qvd);
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName).qvd(qvd);
ENDIF;
LET iFile=1;
DROP TABLE $(vSourceName);
ENDIF;
NEXT File
Next counter
DROP TABLEMeasureTable;
Update Dimension Tab:
TRACE <<<<<Update Dimension Tables >>>>>;
DimTable:
Load
* INLINE
[Dimension
ABC_BI_ITEM
ABC_BI_SALESREPS
];
For counter =1 to NoOfRows('DimTable')
LET vSourceName=Fieldvalue('Dimension',$(counter));
Trace <<<<<Counter=$(counter)Update Table $(vSourceName)>>>>>;
$(vSourceName):
LOAD *;
SQL SELECT *
FROM "$(vDBName)".dbo."$(vSourceName)";
Let nRow=NoOfRows('$(vSourceName)');
IF NoOfRows('$(vSourceName)')>0THEN
TRACE<<<<< nRows=$(nRow)>>>>>;
STORE $(vSourceName) into $(vQvdFolder)$(vSourceName).qvd(qvd);
ENDIF;
DROP TABLE $(vSourceName);
TRACE<<<<<DIMENSION TABLE UPDATED>>>>>;
Next counter
DROP TABLEDimTable;