Loading data only for recent 3 years

[#1]

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

[#2]

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..

sunindia

[sunny_talwar]

‌not sure what you mean? Would you be able to share your script?

[#4]

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;

[sunny_talwar]

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);