// QlikSesnse
// SET vDBLoad=1; SET vCountry='SG';
// SET vDBO='"ERP_PRD"';
// SET vConnection='ERPDB (qlik.admin)';
// SET vP1_QVDFolder='lib://Qlik_DataFolder (qlik_qlik.admin)/04 Data\QVD\$(vCountry)';
// LIB CONNECT TO '$(vConnection)';
// ALL Table:
Tables_Master:
LOAD '$(vDBO).dbo.'&name AS TBL,rows as NO_OF_Rows,name as TABLE_RAW,TABLE_TYPE
,if(SubStringCount(name,'$')=1, SubField(name,'$',1)) as Company
,if(SubStringCount(name,'$')=1, SubField(name,'$',2)) as TABLE_NAME
;
SQL SELECT TBL.object_id, TBL.name name,INF_S.TABLE_TYPE, SUM(PART.rows) AS rows
FROM $(vDBO).sys.tables TBL
INNER JOIN $(vDBO).sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN $(vDBO).sys.indexes IDX ON PART.object_id = IDX.object_id
INNER JOIN $(vDBO).INFORMATION_SCHEMA.TABLES INF_S on INF_S.TABLE_NAME=TBL.name
AND PART.index_id = IDX.index_id and TBL.type='U'
WHERE IDX.index_id < 2 and INF_S.TABLE_TYPE='BASE TABLE'
GROUP BY TBL.object_id, TBL.name,INF_S.TABLE_TYPE
order by 3 desc;
STORE Tables_Master into [$(vP1_QVDFolder)\Tables_Master.qvd](qvd);
DROP Table Tables_Master;
// LIMITED Table:
Tables_Master:
LOAD * INLINE [
TABLE_NAME
G_L Account
Store
Dimension Value
];
// Extraction Script:
Set errormode=1;
For i=1 to FieldValueCount('TABLE_NAME')
LET vTableName=keepchar(upper(FieldValue('TABLE_NAME',$(i))),'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
LET vdbTableName='$(vDBO_2)'&FieldValue('TABLE_NAME',$(i))&'"';
LIB CONNECT TO '$(vConnection)';
Trace $(vdbTableName);
$(vTableName):Load '$(vdbTableName)' AS Table_Name,*; SQL SELECT * FROM $(vdbTableName);
STORE '$(vTableName)' INTO [$(vP1_QVDFolder)\$(vTableName).QVD](QVD);
DROP TABLE $(vTableName);
DISCONNECT;
Next i
Financial Calendar For Extract:
SET vFiscalYearStartMonth = 7;
LET vDateMin = Num(Date(MakeDate(2018,7,1)));
LET vDateMax = Num(Date(MakeDate(2020,12,31)));
LET vDateToday = Num(Today());
TempCalendar1:
LOAD Date($(vDateMin) + RowNo() - 1) AS Date
AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateToday);
Master_Calendar:
Load Distinct Date(Date,'DD-MM-YY') as Date
,LEft(YearName(Date, 1, $(vFiscalYearStartMonth)),4) AS FiscalYear_Num // Fiscal Calendar Year
Resident TempCalendar1;
DROP Table TempCalendar1;
// Date Issue: https://stackoverflow.com/questions/5505935/convert-from-datetime-to-int
FY_Calendar: LOAD FiscalYear_Num
,min(Date)-2 as Fiscal_StartDate
,max(Date)-2 as Fiscal_EndDate
Resident Master_Calendar Group by FiscalYear_Num Order by FiscalYear_Num;
DROP Table Master_Calendar;
//Calendar Loop:
For i=1 to FieldValueCount('FiscalYear_Num')
LET vFiscalYear=FieldValue('FiscalYear_Num',$(i));
LET vFiscalStart=FieldValue('Fiscal_StartDate',$(i));
LET vFiscalEnd=FieldValue('Fiscal_EndDate',$(i));
LIB CONNECT TO '$(vConnection)';
GLENTRY_Year:
LOAD [Entry No_] as GL_Entry_No,* ;
SQL SELECT * FROM $(vDBO_2)G_L Entry"
Where CONVERT(INT,[Posting Date])>=$(vFiscalStart) and CONVERT(INT,[Posting Date])<=$(vFiscalEnd)
and left([G_L Account No_] ,1) >=6;
DISCONNECT;
NEXT i
Drop Table FY_Calendar;
