Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi there,
Date(Today() - x) will give you a 5-digit number for your date. That might now work for your WHERE condition, depending on your data source.
Date(Date(Today() - x)) should give you a regular date (12.03.2012 / 'DD.MM.YYYY' in my case).
Regards, Jakob