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

[paulyeo11]

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;

[Stoyan_Terziev]

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.