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

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;

Read more of this post

Intervalmatch example – with dates

I have two tables Table1,Table2. Table1 has DATE, Table2 has START_DT,END_DT. Want to join these two tables on DATE columns.

Intervalmatch function will help us to make the join in Qlik. If we use left join, we can avoid synthetic tables too. Here is the example.

If you want to test, prepare the test data.

Let me know if you need any more info/clarification.

Table1:
 LOAD COUNTRY,
 DATE,
 C1,
 C2
 FROM Table1;
 Table2:
 LOAD COUNTRY, C3,C4,
 date(START_DT) as START_DT,
 date(END_DT) as END_DT
 FROM Table2;
 Final_Table:
 INTERVALMATCH (DATE) left JOIN LOAD START_DT, END_DT RESIDENT Table2;

left Join (Table1) LOAD distinct * RESIDENT Table2;

DROP TABLE Table2;

How to load latest month Data from Quarter QVDs SalesYYYYQ0X format

//Data load and store into QVD:

Let vQVD='Sales'&Year(Today())&num(ceil(month(Today())/3),'00');
 SalesData:
 LOAD InvoiceNumber, InvoiceLineNumber, InvoiceDate, CustomerID, SalesmanID, ProductID, Quantity,
 Price, TotalPrice,'Sales'&Year(Today())&num(ceil(month(Today())/3),'00') as QVDName
 FROM Data.xlsx (ooxml, embedded labels, table is Sheet2);
 STORE SalesData into $(vQVD).qvd(qvd);
 EXIT SCRIPT;

//Loading latest month Data from latest QVD:

Set vFolder = 'C:\Users\SUDHIKSHA\Desktop\Qlikview\';

load * ,QvdTableName('Sales'&Year(Today())&num(ceil(month(Today())/3),'00')&'.QVD') from [$(File)] (qvd)
 Where month(InvoiceDate)=month(Today());

Data Download this file