Hi All
My below script work fine , only issue is when i try to rename the excel TAB name from BRAND to COUNTRY_A it have issue.
The Situation :-
if i change from :-
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx (ooxml,embedded labels, table is [BRAND]);
To below :-
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx (ooxml,embedded labels, table is [COUNTRY_A]);
i Will keep same Erroe Massage Below :-
Field not found - <Brand_>
MapDescTDS:
Mapping LOAD
[Brand_],
[SubBrand_]
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx(ooxml,embedded labels, table is [COUNTRY_A])
When i have change excel Tab name from BRAND TO COUNTRY_A
and below :-
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx (ooxml,embedded labels, table is [COUNTRY_A]);
it there any other work around ?
Hope some one can advise me.
Paul Yeo
MapDesc:
Mapping LOAD
[Brand_],
[SubBrand_]
FROM MapBrand.xlsx (ooxml,embedded labels, table is [TDS]);
// sales COUNTRY A
sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
ApplyMap('MapDesc',@282:298T,'OTHERS') as [BRAND_C],
@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);
tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT sales;
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;
//*************** Temporary Calendar ***************
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;
