Main tab:
load *,If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',DESCRIPTION) as Description,
If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',SITEID) as [Site ID],
If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',PURCHASEAGENT) as [Purchase Agent],
If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',REQUESTEDBY) as [Requested By],
If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',RFQNUM) as RFQnum,
If(DESCRIPTION like 'U*' Or DESCRIPTION like 'u*',STATUS) as Status;
LOAD
.
.
.(the rest of the fields)
.
.
date(floor(timestamp#(Replace([PRINTDATE],' ',' '), 'DD.MM.YYYY hh:ss:mm'))) as [Print Date]
FROM
<filename>
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Dates tab:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min([Print Date]) as minDate,
max([Print Date]) as maxDate
Resident RFQ;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS [Print Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
date(monthstart(TempDate), 'MM/YYYY') as MonthYearNum,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
