Useful sub-routines to prevent re-writing script codes for frequent script logics

[cheenu_janakira]

QVS from Github here.

Sub-routines included:

(1)  DropAllTables(parTableExceptions_PIPE_Delimited)

• Drop all tables, except the table names you pipe-delimit in the parameter.

 (2)  MappingFromQVD(parQVDTargetFolder,parQVDName,parMappingTableName_Optional,parMappingField1,parMappingField2)

• If mapping from a QVD will use a resident reload to go faster for QVDs of more that 100,000 rows.

(3)  QVDFilterDate(parScriptLoadStatementSubName,parQVDStartDate,parQVDEndDate,parQVDDateField_Name,parQVDDateField_NUMorDateFormat)

• Load faster from large QVDs using a support table with dates.

(4)  QVDFilterField(parScriptLoadStatementSubName,parQVDFilterONField_Name,parQVDFieldFilters_PIPE_Delimited)

• Load faster from large QVDs using a support table with field filter-values (N.B. Be cautious with field data types).

(5)  QVDFileNamesList(parQVDTargetFolder,parQVDNamePrefix,parLoadLastNFiles)

• Get a list of filenames in a folder that have naming-convention "[Filename_Prefix]_[YYYYMM].qvd".

(6)  QVDFileNamesLoad(parQVDTargetFolder,parQVDNamePrefix,parLoadLastNFiles,parOPTIONALsubName)

• Load a list of QVDs in a folder that have naming-convention "[Filename_Prefix]_[YYYYMM].qvd".

(7)  DistinctFieldValues(parField)

• Create a table with unique field-values.

(8)  DatesToBeFilled(parDateFieldRequiringFill,parDESC_or_ASC_Date_Ordering).

• Create a table that gives you "missing date" values and the earliest previous (ASC) or earlies next (DESC) date. 

(9) QVDCreateYMSlices(parTableNameNEEDToNameAsTargetQVDs,parTargetFolder,parDateFieldNameToCreateQVDSlices,parPRIMARYkey)

• Automatically create YYYYMM QVDs from a table you have loaded in the script.

(999) QVDCreateYMSlicesFromQVD(parSourceQVDFolder,parSourceQVDFile,parTargetFolder,parDateFieldNameToCreateQVDSlices)

• Create YYYYMM QVD slices from a big QVD to get you started with an incremental YYYYMM QVD storing strategy.