Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

what if i never load sales table to time line , what is the consequences ?

Hi All

I have below script work fine , But i notice that in the TIME LINE load script , i only Resident GL_TABLE.

tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT GL_TABLE;

May i know should i also load sales Table to TIME LINE ?

Paul Yeo


// sales table
sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
@282:298T as [BRAND],
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

// GL table
GL_TABLE:
LOAD
'TDS' as SOURCE,
date#(MakeDate(@53:57T,if(@50:52T=13,12,@50:52T) ,1),'DD/MM/YYYY')as date,
@124:129T as [Reporting Code],
If([@124:129T]>=1 and [@124:129T]<=15,'P_Revenue_T') as [P_Revenue_T],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\FS_TDS_.TXT (ansi, fix, no labels, header is 0, record is line);

// Time Line
tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT GL_TABLE;

MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;

DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

MasterCalendar:
LOAD TempDate AS date,
day([TempDate]) as [day],
num(month([TempDate])) as [month],
Y AS year
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

1 Reply
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hi Paul,

 

The general advantage of a Master Calendar created for the time range between min and max reporting date

Vs

Calendar created off the [date] field

is so you make sure that you are not leaving out any dates not present in your fact data. (Such as weekends are often zero transactions days)

 

There is a bit of room for improvement to the code you are struggling with. For example:

Use Order By date to peek the first and last record instead of doing min/max load.

Also - generate your calendar with YearStart (min date) to YearEnd(max date) for consistency.

 

 I hope that helps!

Kind regards,

S.T.