Help with my script to only load in last 10 weeks data.

[hopkinsc]

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?

[CELAMBARASAN]

Hi,

     Are you looking for this type of where condition?

     WHERE Date(Cal_Date) >= Date(WeekStart(Today(),-10));

Celambarasan

[hopkinsc]

Hi,

I think the where i am using is correct for what i want, I think then i will have to use a where exists (TradingDate) on the PosTransactionHeader table, but if i try this it doesnt load ANY data in.

Im confused Why.

[swuehl]

I know that you have said in the other thread that both tables have the same format for TradingDate, but please double check that they not only have the same date format, but also the numerical representation is limited to integers for boths (only dates, not timestamps). Check by applying num(TradingDate) as expression to a TradingDate list box after loading both tables.

It's just because a mismatch Date/Timestamp field values is one of the Top issues when linking / using where clause.

[CELAMBARASAN]

Hi,

     Use the same where condition for POSTransactionHeader table.

Have you checked whether  POSTransactionHeader returns results as per your script?

Also check without where exists it returns results?

Celambarasan

[hopkinsc]

Hi Stefan,

Here is the two date fields i am using.

Cal_Date (Renaming this to TradingDate)

TradingDate

Not sure what else to check?

[hopkinsc]

Hi Celambarasan,

If i have the WHERE clause on the calender table then the data loads in as expected but with all data for PosTransactionHeader.

If i add the WHERE EXISTS clause to PosTransactionHeader, then nothing is loaded in.

I have just tried using the same WHERE clause that i am using in the calender on the PosTransactionHeader table, but again no data is loaded in.

Thanks

[CELAMBARASAN]

Hi,

     You mean PosTransactionHeader table has no data?

Celambarasan

[swuehl]

But when using the where clause in the PosTransactionHeader load, you replaced CalDate with TradingDate?

Another stupid one:

Do you really have dates for the requested date range in both tables?

The TradingDates you posted above stop mid Sep 2011.

Does you CalDates range is up to today?