1 Reply Latest reply: Mar 12, 2012 10:33 AM by Jakob Fabian RSS

    Script help with only loading in last 10 weeks data

    Chris Hopkins

      Hi all,

       

      I am looking for some help in my script. I only want to load in the last 10 weeks worth of data.

      Here is my script..

       

      SET cFileName = $(vcGlobalQVD)CapperCalender.QVD;
      IF QvdCreateTime('$(cFileName)') >= 0 THEN

          CapperCalender:
          LOAD Cal_Year,
               Cal_Period,
              Cal_Week,
               Cal_YearDay,
               Cal_Date AS TradingDate,
               Cal_CurrentEPOSYear,
               Cal_CurrentEPOSWeek,
               Cal_CurrentYear,
               Cal_LastYear,
               Cal_CurrentWeek,
               Cal_LastWeek,
               Cal_CurrentPeriod,
               Cal_LastPeriod,
               Cal_ThisYTD,
               Cal_DayCount,
               Cal_WeekCount,
               Cal_PeriodCount,
               [Cal_Year-Week],
               [Cal_Year-Period]
          
          FROM $(cFileName) (qvd) WHERE Date(Cal_Date) >= Date(Today() -70);

      ENDIF

       

       

      SET cFileName = $(vcRetailQVD)POSTransactionHeader_*.QVD;
      IF FileTime ('$(cFileName)') >= 0 THEN
        POSTransactionHeader:
      LOAD StoreNumber,
           TransId,
           TransStartTime,
           EmployeeId,
           TillNo,
           TransStatus,
           OverallDiscount,
           Nolines,
           TradingDate,
           ZreadRef,
           TrainingMode,
           TrainerId,
           %LkStoreTransID,
           %lkStoreEmployee
      FROM $(cFileName) (qvd);

      ENDIF

       

       

      SET cFileName = $(vcRetailQVD)POSTransactionDetail_*.QVD;
      IF FileTime('$(cFileName)') >= 0 THEN
      POSTransactionDetail:
      LOAD 
           LineNo,
           EANCode AS EAN,
           LineType,
           ItemPrice,
           SalesValue,
           SalesQuantity,
           ItemCost,
           SaleDescription,
           PriceOverride,
           AuthorityId,
           IssueNumber,
           StaffDiscountValue,
           PerceivedAge,
           DateOfBirth,
           DiscountGroup,
           %LkStoreTransID,
           %LkStoreTranLineAge    
      FROM $(cFileName) (qvd) where Exists(%LkStoreTransID);
      ENDIF

       

       

      SET cFileName = $(vcRetailQVD)POSPaymentTransaction_*.QVD;
      IF FileTime('$(cFileName)') >= 0 THEN
        POSPaymentTransaction:
      LOAD
           TenderType,
           Amount,
           AmountTendered,
           CashbackAmount,
           %LkStoreTransID
         FROM $(cFileName) (qvd)where Exists(%LkStoreTransID);
      ENDIF

       

      I have put in the first table a where command which i think is correct which should only load the calender in for the last 70 days but i'm not sure what else to do.

       

      Can anyone help please?