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

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

hopkinsc — Mon, 12 Mar 2012 11:45:49 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?

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

CELAMBARASAN — Mon, 12 Mar 2012 12:03:20 GMT

Hi,

Are you looking for this type of where condition?

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

Celambarasan

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

hopkinsc — Mon, 12 Mar 2012 12:32:29 GMT

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.

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

swuehl — Mon, 12 Mar 2012 12:43:51 GMT

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.

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

CELAMBARASAN — Mon, 12 Mar 2012 12:51:38 GMT

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

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

hopkinsc — Mon, 12 Mar 2012 13:24:44 GMT

Hi Stefan,

Here is the two date fields i am using.

Cal_Date (Renaming this to TradingDate)

TradingDate

Not sure what else to check?

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

hopkinsc — Mon, 12 Mar 2012 13:32:12 GMT

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

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

CELAMBARASAN — Mon, 12 Mar 2012 13:34:26 GMT

Hi,

You mean PosTransactionHeader table has no data?

Celambarasan

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

swuehl — Mon, 12 Mar 2012 13:40:35 GMT

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?