13 Replies Latest reply: Jun 28, 2015 4:48 PM by Sunny Talwar RSS

    Loading data only for recent 3 years

    venkat sai

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

       

      i got all values ...even i got missing dates also..!! need to change date format in script..

       

      i.png

       

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

      iiii.png 

      iii.png

      thanks

      venky

        • Re: Loading data only for recent 3 years
          Sunny Talwar

          From what I see, you are restricting only you master calendar to be include data from 2009 and forward, but the fact table may still have those have that date. You can restrict your fact table by using a Right Keep. Try this:

           

          MasterCalendar:

          Right Keep (FactTable) //Assuming that the fact table is loaded before Master Calendar

          LOAD OrderDate,

                    Week,

                    Year,

                    Month,

                    MonthNo,

                    DayofYear,

                    MonthYear,

                    YearMonth,

                    Day,

                    Quarter,

                    YearWeek,

                    WeekDay,

                    WeekDayNo,

                    WeekInMonth,

                    CurYTDFlag,

                    LastYTDFlag,

                    CurMTDFlag,

                    LastMTDFlag

          FROM

          [C:Users\venkatsai\Desktop\Project\QVDs\MasterCalendar.qvd] (qvd)

          Where Year >= 2009;

           

          See if this helps

           

          Best,

          Sunny

            • Re: Loading data only for recent 3 years
              venkat sai

              Thanks bro..

              how to change to time format?

                • Re: Loading data only for recent 3 years
                  Sunny Talwar

                  You want to change it from MM/DD/YYYY hh:mm:ss TT to MM/DD/YYYY????

                   

                  Try this:


                  Date(Floor(Date#(YourDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as YourDate

                    • Re: Loading data only for recent 3 years
                      venkat sai

                      thanks bro

                      how to allocate variable to Max(Year)...and call this variable in where clause?

                      • Re: Loading data only for recent 3 years
                        venkat sai

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

                         

                        stalwar1

                          • Re: Loading data only for recent 3 years
                            Sunny Talwar

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

                              • Re: Loading data only for recent 3 years
                                venkat sai

                                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;

                                 

                                 

                                 

                                33333.png

                                  • Re: Loading data only for recent 3 years
                                    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);