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 … Continue reading Incremental Load Qlikview – Help Example

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 … Continue reading Storing Date or month in a variable

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; … Continue reading Sample ETL transformation with Qlikview tables

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, … Continue reading How to load source files dynamically?

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 = … Continue reading How to load QVDs dynamically based on Date and its availability

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”