QlikView / QlikSense : Extract - All / Limited Table from Databases : QVD Generator + CALENDAR Loop Extract

// 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;

QV - Master Calendar Scripts !!!

Master Calendar Scripts All Time :)

Quarter_Map:
Mapping
LOAD * INLINE [
    Month, Quarter
    Jan, Q4
    Feb, Q4
    Mar, Q4
    Apr, Q1
    May, Q1
    Jun, Q1
    Jul, Q2
    Aug, Q2
    Sep, Q2
    Oct, Q3
    Nov, Q3
    Dec, Q3
];

///////////////////////////////////////

SET vDateMin = Num(date('01/01/1950','DD/MM/YYYY'));
SET vDateMax = Num(date('31/12/2050','DD/MM/YYYY'));
LET vDateToday = Num(Today()); 
 

//LET vDateMin = Num(Peek('Open_DateTime', 0, 'Interval_Table'));
//LET vDateMax = Num(Peek('Close_DateTime', -1, 'Interval_Table'));
//LET vDateToday = Num(Today()); 

TempCalendar1: 
LOAD
$(vDateMin) + RowNo() - 1 AS Date_Key, 
Date($(vDateMin) + RowNo() - 1) AS Date 
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Master_Calendar:
load Date_Key
       ,Date
       ,Date as CalendarDate
       ,Day(Date) as CalendarDay
       ,Week(Date) as CalendarWeek
       ,WeekName(Date) as CalendarWeekName
       ,month(Date) as CalendarMonth
       ,MonthName(Date) as CalendarMonthName
       ,MonthName(Date) as CalendarPeriod
       ,Quartername(Date) as CalendarQuarterName
       ,ApplyMap('Quarter_Map',num#(month(Date)))as CalendarQuarter 
       ,year(Date) as CalendarYear
       ,WeekDay(Date) as CalendarWeekDay
       ,text(weekday(Date)) as Week_Day1

       ,WeekDay(Date) as CalWeekDay
       ,Week(Date) as CalWeek
       ,week(Date+3) as CalRWeek    
       ,WeekName(Date) as CalWeekName
       ,WeekName(Date,1,4) as CalWeekRName    //4=Fri     // Friday - Thursday Business Week
       ,MakeWeekDate(Year(Date),Week(Date)) as CalWkSD       
       ,num(MakeWeekDate(Year(Date),Week(Date))) as CalWkSDNum            
       ,MakeWeekDate(Year(Date+3),Week(Date+3),-3) as CalWkRSD      
       ,num(MakeWeekDate(Year(Date+3),Week(Date+3),-3)) as CalWkRSDNum  


Resident TempCalendar1;
DROP Table TempCalendar1;


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Thx for the QlikCommunity Post : by Jagan Mohan 

https://community.qlik.com/docs/DOC-9146

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

"Master Calendar with every 30 Minutes"

LET vMinDate = Num(MakeDate(2014,1,1));  // Calendar Start Date
LET vMaxDate = Num(Today()); // Calendar End Date
LET vDays = vMaxDate - vMinDate + 2;  // Calculating number of days between Start & End Dates

Calendar:
LOAD Date(Floor(TimeStamp)) AS Date,
TimeStamp,
Hour(TimeStamp) AS Hour,
Minute(TimeStamp) AS Minute;
LOAD
    Timestamp($(vMinDate) + (RecNo() - 1)/48) as TimeStamp

AUTOGENERATE 48 * $(vDays);