Script help with only loading in last 10 weeks data

hopkinsc — Mon, 12 Mar 2012 11:31:48 GMT

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?

Script help with only loading in last 10 weeks data

jjfabian — Mon, 12 Mar 2012 14:33:46 GMT

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

topic Script help with only loading in last 10 weeks data in QlikView

Script help with only loading in last 10 weeks data

Script help with only loading in last 10 weeks data