Incremental Load Qlikview – Help Example

Show time in Excel file: =TEXT(TODAY(),”d-mmmm-yyyy”)&” “&TEXT(NOW(),”h:mm AM/PM”)

Data:

PrimaryKey X Y ModificationTime
pk1 x1 y1 12/23/2017 0:00
pk2 x2 y2 12/23/2017 0:00

First Load:

QV_Table:
LOAD PrimaryKey, X, Y, ModificationTime
FROM File.xlsx (ooxml, embedded labels, table is Data);
STORE QV_Table into File.qvd(qvd);
Let LastExecTime = num(Now());

pk2 row is updated X,Y values to x22,y22

PrimaryKey X Y ModificationTime
pk1 x1 y1 12/23/2017 0:00
pk2 x2 y2 12/23/2017 0:00
pk2 x22 y22 12/28/2017 15:59

Incremental Script:

Let ThisExecTime = num(Now());
QV_Table:
LOAD PrimaryKey, X, Y, ModificationTime
FROM File.xlsx (ooxml, embedded labels, table is Data)//; EXIT Script;
WHERE num(ModificationTime) >= $(LastExecTime)
AND num(ModificationTime) < $(ThisExecTime);
Concatenate 
LOAD PrimaryKey, X, Y, ModificationTime
FROM File.qvd (qvd)
WHERE NOT EXISTS(PrimaryKey);
Inner Join 
LOAD PrimaryKey
FROM File.xlsx (ooxml, embedded labels, table is Data);

If ScriptErrorCount = 0 then 
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime; 
End If
//EXIT Script;

Advertisements

Storing Date or month in a variable

[CRESA Dept Expenses Temp]:
LOAD Country, 
 Category, 
 MonthName, 
 Dept_Expenses
FROM
[..\QVD\CRESA_Dept_Expenses.qvd]
(qvd);

[CRESA Dept Expenses]:
load *,ApplyMap('MappingLoad', Country, null())&[Month Name] as Key;//,year([Month Name]) as Year,month([Month Name]) as Month;
Load 
Country,
//If(Len(Country)='', Peek(Country,-1),Country) as Country,
//If(Len(Dept_Expenses)=0, Peek(Dept_Expenses,-1),Dept_Expenses) as [Dept Expenses],
Dept_Expenses as [Dept Expenses],
Category as [Dept Category],
//MonthName,
//Dept_Expenses as [Dept Expenses],
MonthName(left(MonthName,5)) as [Month Name],
if(len(MonthName)=5,'Actual',
if(len(MonthName)=6 and right(MonthName,1)=1,'Budget',
if(len(MonthName)=6 and right(MonthName,1)=2,'Forecast'
))) as [Dept Flag]
Resident [CRESA Dept Expenses Temp];

drop table [CRESA Dept Expenses Temp];
//EXIT Script;

HeadCount:
Load [Dept Flag],date(Date#([Month Name],'MMM YYYY'),'MMM-YY') as MonthName,[Dept Expenses]
Resident [CRESA Dept Expenses]
where len(trim([Dept Expenses]))>0 and [Dept Flag]='Actual'
order by [Month Name] desc;
LET vHeadCount=peek('MonthName',0,'HeadCount');

drop table HeadCount;

CRESADeptExpenses:

Load [Dept Flag],date(Date#([Month Name],'MMM YYYY'),'DD-MMM-YY') as MonthName,[Dept Expenses]//,max(date(Date#([Month Name],'MMM YYYY'),'DD-MMM-YY')) as MaxDate
Resident [CRESA Dept Expenses]
where len(trim([Dept Expenses]))>0 and [Dept Flag]='Actual'
//Group by [Dept Flag],[Month Name]
order by [Month Name] desc;
LET vCRESADeptExpenses=peek('MonthName',0,'CRESADeptExpenses');
LET vMaxDateDEPT=peek('MonthName',0,'CRESADeptExpenses');
drop table CRESADeptExpenses;
//EXIT Script;

Sample ETL transformation with Qlikview tables

Here is a sample ETL transformation with Qlikview between tables

Temp:
LOAD DISTINCT PRODUCT_ID AS NEW_PROD_ID,
 PRODUCT_ID & '-' & Date(LATEST_DATE, 'DD-MMM-YYYY') AS Key,
 PRODUCT_NAME,
 Date(LATEST_DATE, 'DD-MMM-YYYY') AS AS_OF_DATE,
 Date(LATEST_DATE, 'DD-MMM-YYYY') AS DET_AS_OF_DATE,
 C1,
 C2,
 C3
FROM
$(QVDPath)\TBL1.qvd (qvd)
WHERE Com_KEY_PRODUCT = 'Y' and num(Date(LATEST_DATE, 'DD-MMM-YYYY'))>=43082;

TempAsOfDate:
LOAD 
Date(LATEST_DATE, 'DD-MMM-YYYY') AS AS_OF_DATE
FROM
$(QVDPath)\TBL2.qvd (qvd)
where num(Date(LATEST_DATE, 'DD-MMM-YYYY'))>=43082;

INNER JOIN(Temp)
LOAD DISTINCT
 AS_OF_DATE
RESIDENT TempAsOfDate;

DROP TABLE TempAsOfDate;

TempHIST:
LOAD 
NEW_PROD_ID & '-' & Date(LATEST_DATE, 'DD-MMM-YYYY') AS Key,
NEW_PROD_ID AS HIST_NEW_PROD_ID,
Date(LATEST_DATE, 'DD-MMM-YYYY') AS HIST_AS_OF_DATE,
1 AS TempFlag
FROM
$(QVDPath)\TBL3.qvd (qvd);

LEFT JOIN(Temp)
LOAD
DISTINCT
*
RESIDENT TempHIST;

DROP TABLE TempHIST;

NotStartedPRODUCTs:
LOAD DISTINCT
*
WHERE Flag = 1;
LOAD
*,
If(IsNull(TempFlag), 1, 0) AS Flag
RESIDENT Temp;

DROP TABLE Temp;

Temp1:
LOAD
 PROD_ID & '-' & Date(LATEST_DATE, 'DD-MMM-YYYY') AS Key,
 SAL,
 COMM,
 BUDGET
FROM $(QVDPath)\TBL4.qvd (qvd);

LEFT JOIN(NotStartedPRODUCTs)
LOAD
Key,
Sum(SAL) AS SAL,
Sum(COMM) AS COMM,
Sum(BUDGET) AS BUDGET
RESIDENT Temp1
GROUP BY Key;

DROP TABLE Temp1;

How to load source files dynamically?

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY 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';

SET vFilePath = '..\QVD\Source files';
SET vQVDPath = '..\QVD';
LET vCurrentMonth = Text(Date(Today(), 'YYYY-MM'));




IF Alt(FileSize(vQVDPath & '\'& 'Table1.qvd'), 0) = 0 THEN
 Table1:
 LOAD *,
 Date(Floor(Date#(LATEST_DATE, 'DD/MM/YYYY hh:mm'))) AS LATEST_DATE_FORMAT,
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*Table1.csv]
 (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
 
 Concatenate(Table1)
 LOAD *,
 Date(Floor(Date#(LATEST_DATE, 'YYYY-MM-DD hh:mm:ss TT'))) AS LATEST_DATE_FORMAT, //2015-12-21 09:00:00 AM
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*Table1.dat]
 (txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 1 lines);
ELSE
 Table1:
 LOAD
 *
 FROM $(vQVDPath)\Table1.qvd (qvd)
 WHERE LATEST_DATE_FORMAT < MonthStart(Today());
 
 
 IF Alt(FileSize('$(vFilePath)' & '\*$(vCurrentMonth)*Table1.dat'), 0) > 0 THEN 
 Concatenate(Table1)
 LOAD
 *,
 Date(Floor(Date#(LATEST_DATE, 'YYYY-MM-DD hh:mm:ss TT'))) AS LATEST_DATE_FORMAT,
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*$(vCurrentMonth)*Table1.dat] //Source.2015-12-21_Table1
 (txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 1 lines);
 ENDIF
 
 IF Alt(FileSize('$(vFilePath)' & '\*$(vCurrentMonth)*Table1.csv'), 0) > 0 THEN 
 Concatenate(Table1)
 LOAD *,
 Date(Floor(Date#(LATEST_DATE, 'DD/MM/YYYY hh:mm'))) AS LATEST_DATE_FORMAT,
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*$(vCurrentMonth)*Table1.csv]
 (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
 ENDIF

ENDIF

STORE Table1 INTO $(vQVDPath)\Table1.qvd;
DROP TABLE Table1;
Exit Script;

How to load QVDs dynamically based on Date and its availability

In general , we will load the data in QVDs with daily basis or monthly basis with suffix.

Sometimes we need to pick latest QVDs and load into the dashboard.

I have QVDs like QVD_2017_01,QVD_2017_02,QVD_2017_03,QVD_2017_04,QVD_2017_04 and so on…

I want to load latest QVDs like last 3 months if it exists.

How to achieve this requirement?

FOR i = 0 to vMaxMonthNumber 
 let Date_Suffix = date(floor(monthstart(today(), -vMaxMonthNumber+i)), 'YYYY_MM'); 
 
 LET vFileNotAvailable=isnull(QvdCreateTime('$(vQVDPath)\QVD Folder\Table1_$(Date_Suffix).qvd')); // if qvd exists then 0 else -1
 
 IF not $(vFileNotAvailable) then
 
 Table1:
 load *
 FROM $(vQVDPath)\QVD Folder\Table1_$(Date_Suffix).qvd (qvd);
 end if 
 
 LET vFileNotAvailable=isnull(QvdCreateTime('$(vQVDPath)\QVD Folder\Table2_$(Date_Suffix).qvd')); // if qvd exists then 0 else -1
 
 IF not $(vFileNotAvailable) then 
 Table2:
 load *
 FROM $(vQVDPath)\QVD Folder\Table2_$(Date_Suffix).qvd (qvd);

end if;
NEXT i;

Custom Functions and Master Calendar

//custom defined functions
 Let fGreaterNumber = 'if(num($2) >= num($1), num($2), num($1))';
 // How to use:
 // =$(fGreaterNumber(30,100)) give you the greater number of the two, which is 100

Let fSmallerNumber = 'if(num($2) <= num($1), num($2), num($1))';
 // How to use:
 // =$(fGreaterNumber('1/1/2016','1/1/2017')) give you 1/1/2016 which is the smaller date (also number)

Let fRemoveDate = 'left($1, Index($1, ''|'')-1)';
 // input: 68332|2016/12/12
 // out put: 68332

// custom function to calculate month difference, can be used in expression too
 SET fMonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))));
 // to use in expression: =$(fMonthDiff(Today(), Today() + 65))

set fRoundToMinuteTime = Time(round(Frac($1),1/24/60),'h:mm');

MinMax: 

LOAD   min(DATE) as MinDate, 

max(DATE) as MaxDate

FROM Table1;

let vMaxDate = num(Peek('MaxDate', 0, 'MinMax'));

let vMinDate = num(Peek('MinDate', 0, 'MinMax'));

let vToday = Date(Peek('MaxDate', 0, 'MinMax'));

Let vCurrentMonth = text(Date(Peek('MaxDate', 0, 'MinMax'), 'MMM'));
 TempCalendar:

Load date($(vMinDate) + rowno() - 1) As TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) + 1; 

Drop table MinMax;
 MasterCalendar:

LoadTempDate as Date,

1 as Date_Flag,

Day(TempDate) as Day,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Weekday(TempDate) as WeekDay,

WeekStart(TempDate) as WeekStart,

date(WeekEnd(TempDate), 'DDMMM') as WeekEnding,

date(WeekStart(TempDate), 'DDMMM') & '-' & date(WeekEnd(TempDate), 'DDMMM') as WeekName,dual(text(Date(monthstart(TempDate), 'MMM-YY')), $(fMonthDiff($(vMinDate), TempDate))) as MonthYear, // auto assign ranking to month-year text, in expression: max(MonthYear) does return the right value$(fMonthDiff($(vMinDate), TempDate)) as MonthNumber

Resident TempCalendar

Order by TempDate ASC;

drop table TempCalendar;

Table1:
 load *, Date($(fGreaterNumber(START_DT, $(vMinDate)))+IterNo()-1) as DATE
 From Table2
 While IterNo() <= $(fSmallerNumber(END_DT, $(vMaxDate))) - $(fGreaterNumber(START_DT, $(vMinDate))) + 1;

Continue reading “Custom Functions and Master Calendar”