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

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;

qlik sense interview questions

https://intellipaat.com/interview-question/qliksense-interview-questions/

http://us.analytics8.com/insights/answers-to-your-qlik-sense-3.2-questions

https://tekslate.com/interview-questions-on-qliksense/

Set Analysis

Please check the below table with Month,Cost,Headcount,YTD Factor.

Calculated Value=

[Current month cost] * ([Current month Headcount] – [Previous month Headcount]) * [YTD Factor]

=
 sum( {$<Month={"$(=Date(Max(Month),'MMM-YY'))"}>} Cost )
 *
 (
 sum( {$<Month={"$(=Date(Max(Month),'MMM-YY'))"}>} Headcount )
 -
 Sum( {$<Month={"$(=Date(AddMonths(Max(Month),-1),'MMM-YY'))"}>} Headcount )

) * sum( {$<Month={"$(=Date(Max(Month),'MMM-YY'))"}>} [YTD Factor] )

 

Month Cost Headcount YTD Factor
Dec-17 200 105
Jan-17 150 100 8.5
Feb-17 180 101 7.5
Mar-17 200 99 6.5
Apr-17 140 98 5.5
May-17 135 100 4.5
Jun-17 240 104 3.5
Jul-17 180 110 2.5
Aug-17 190 105 1.5
Sep-17 240 105 0.5

Top 1 record

=Sum({1<Customer={“=Rank(SUM({1}Sales),4)=1”}>} Sales)

=Only({1<Customer={“=Rank(SUM({1}Sales),4)=1”}>} Customer)

=Sum({<Customer={“=Rank(SUM(Sales),4)=1”}>} Sales)