Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello friends, i got a issue while loading data from master calendar..
i need to load Date recent 3 three years..with filling missing dates..
so far i got this result...pls check...
i got all values ...even i got missing dates also..!! need to change date format in script..
2) When i load that only for recent 3 years...iam missing dates..please someone help me to filling values and setting variable for year for recent year...
thanks
venky
Bro...When i used
Date(Floor(Date#(YourDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as YourDate
Only Customer Table data is visible...No data is loading from other tables..
not sure what you mean? Would you be able to share your script?
Orders:
LOAD OrderID,
CustomerID,
ShipperID,
EmployeeID,
Freight,
Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate
FROM
[$(vQvdPath)Orders.qvd]
(qvd);
OrderDetails:
LOAD ProductID,
UnitPrice,
OrderID,
Quantity,
Discount
FROM
[$(vQvdPath)OrderDetails.qvd]
(qvd);
Categories:
LOAD CategoryID,
CategoryName,
Description
FROM
[$(vQvdPath)Categories.qvd]
(qvd);
Customers:
LOAD CustomerID,
CompanyName,
ContactName,
Address,
City,
StateProvince,
PostalCode,
Country,
Phone,
Fax
FROM
[$(vQvdPath)Customers.qvd]
(qvd);
Employee:
LOAD EmployeeID,
[Last Name],
[First Name],
Title,
[Hire Date],
Office,
Extension,
[Reports To],
[Year Salary]
FROM
[$(vQvdPath)Employee.qvd]
(qvd);
Office:
LOAD Office,
OfficeAddress,
OfficePostalCode,
OfficeCity,
OfficeStateProvince,
OfficePhone,
OfficeFax,
OfficeCountry
FROM
[$(vQvdPath)Office.qvd]
(qvd);
Products:
LOAD ProductID,
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitCost,
Prod_UnitPrice,
UnitsInStock,
UnitsOnOrder
FROM
[$(vQvdPath)Products.qvd]
(qvd);
Shippers:
LOAD ShipperID,
Shipper_CompanyName
FROM
[$(vQvdPath)Shippers.qvd]
(qvd);
MasterCalendar:
Right Keep (Orders)
LOAD
OrderDate,
Week,
Year,
Month,
MonthNo,
DayofYear,
MonthYear,
YearMonth,
Day,
Quarter,
YearWeek,
WeekDay,
WeekDayNo,
WeekInMonth,
CurYTDFlag,
LastYTDFlag,
CurMTDFlag,
LastMTDFlag
FROM
[$(vQvdPath)MasterCalendar.qvd]
(qvd) Where Year>=($(vMaxYear)-2);
Temp:
LOAD Max(Year) as maxYear
Resident MasterCalendar;
LET vMaxYear = Num(Peek('maxYear', 0, 'Temp'));
DROP Table Temp;
I can see why Orders table may be dropped completely, but not sure why the other tables are having trouble loading in. Do you not get the same problem if you don't use Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate and instead just use OrderDate without any conditions? I suggest running the script and generate logfile and use logfile to troubleshoot as well.
I am made few changes to the script, see if those changes help:
Orders:
LOAD OrderID,
CustomerID,
ShipperID,
EmployeeID,
Freight,
Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate
FROM
[$(vQvdPath)Orders.qvd]
(qvd);
OrderDetails:
LOAD ProductID,
UnitPrice,
OrderID,
Quantity,
Discount
FROM
[$(vQvdPath)OrderDetails.qvd]
(qvd);
Categories:
LOAD CategoryID,
CategoryName,
Description
FROM
[$(vQvdPath)Categories.qvd]
(qvd);
Customers:
LOAD CustomerID,
CompanyName,
ContactName,
Address,
City,
StateProvince,
PostalCode,
Country,
Phone,
Fax
FROM
[$(vQvdPath)Customers.qvd]
(qvd);
Employee:
LOAD EmployeeID,
[Last Name],
[First Name],
Title,
[Hire Date],
Office,
Extension,
[Reports To],
[Year Salary]
FROM
[$(vQvdPath)Employee.qvd]
(qvd);
Office:
LOAD Office,
OfficeAddress,
OfficePostalCode,
OfficeCity,
OfficeStateProvince,
OfficePhone,
OfficeFax,
OfficeCountry
FROM
[$(vQvdPath)Office.qvd]
(qvd);
Products:
LOAD ProductID,
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitCost,
Prod_UnitPrice,
UnitsInStock,
UnitsOnOrder
FROM
[$(vQvdPath)Products.qvd]
(qvd);
Shippers:
LOAD ShipperID,
Shipper_CompanyName
FROM
[$(vQvdPath)Shippers.qvd]
(qvd);
TempMasterCalendar:
LOAD
OrderDate,
Year
FROM
[$(vQvdPath)MasterCalendar.qvd]
(qvd) Where Year>=($(vMaxYear)-2);
Temp:
LOAD Max(Year) as maxYear
Resident MasterCalendar;
LET vMaxYear = Num(Peek('maxYear', 0, 'Temp'));
DROP Tables Temp, TempMasterCalendar;
MasterCalendar:
Right Keep (Orders)
LOAD
Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate,
Week,
Year,
Month,
MonthNo,
DayofYear,
MonthYear,
YearMonth,
Day,
Quarter,
YearWeek,
WeekDay,
WeekDayNo,
WeekInMonth,
CurYTDFlag,
LastYTDFlag,
CurMTDFlag,
LastMTDFlag
FROM
[$(vQvdPath)MasterCalendar.qvd]
(qvd) Where Year>=($(vMaxYear)-2);