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:\Manie\ABC\QV_REPORT\PLS\QVD’;set vQvdFolder=’D:\Watson\ABC\QV_REPORT\BDS\QVD’; //The location is on Watson’s own PCSET 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 THENLET vStartYear=Year(now())-2;ELSELET 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[MeasureABC_BI_INV_LINESABC_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 DateNEXT counterDROP 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[MeasureABC_BI_INV_LINESABC_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[MeasureABC_BI_INV_LINESABC_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 FileNext counterDROP TABLEMeasureTable;Update Dimension Tab:TRACE <<<<<Update Dimension Tables >>>>>;DimTable:Load* INLINE[DimensionABC_BI_ITEMABC_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 counterDROP 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:\Manie\ABC\QV_REPORT\PLS\QVD’;set vQvdFolder=’D:\Watson\ABC\QV_REPORT\BDS\QVD’; //The location is on Watson’s own PCSET 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 THENLET vStartYear=Year(now())-2;ELSELET 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[MeasureABC_BI_INV_LINESABC_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 DateNEXT counterDROP 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[MeasureABC_BI_INV_LINESABC_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[MeasureABC_BI_INV_LINESABC_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 FileNext counterDROP TABLEMeasureTable;Update Dimension Tab:TRACE <<<<<Update Dimension Tables >>>>>;DimTable:Load* INLINE[DimensionABC_BI_ITEMABC_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 counterDROP TABLEDimTable;
，人生就是要感受美丽的善良的，丑恶的病态的。
