topic Where Exists problem in QlikView

Where Exists problem

hopkinsc — Thu, 01 Mar 2012 10:13:51 GMT

Hi, i have the following script..

SET cFileName = $(vcGlobalQVD)CapperCalender.QVD;
IF FileTime('$(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, 
     //TransStartDate, 
     TransStartTime, 
     EmployeeId, 
     TillNo, 
     TransStatus, 
     OverallDiscount, 
     Nolines, 
     TradingDate, 
     ZreadRef, 
     TrainingMode, 
     TrainerId, 
     %LkStoreTransID, 
     %lkStoreEmployee
FROM $(cFileName) (qvd)WHERE Exists (TradingDate);
ENDIF
It doesn't seem to load any data in...

 
 The WHERE in the first part is supposed to only load in calender dates for a rolling 70 period.
 
 Then the WHERE EXISTS in the 2nd part is supposed to only load in data for the dates that are in the calender..
 
 
 But it loads no data in and i am now lost...
 
 Can anyone help please?
 
 Thanks
 
 Chris

Where Exists problem

jagan — Thu, 01 Mar 2012 10:38:39 GMT

Hi,

check this condition is satisfying

IF FileTime('$(cFileName)') >= 0 THEN

by using

Edit Script -> Debug -> Set breakpoint and check whether this statements get executed are not.

Hope this helps you.

Regards,

Jagan.

Where Exists problem

SunilChauhan — Thu, 01 Mar 2012 10:49:55 GMT

use below

IF FileTime '$(cFileName)' >= 0

if not load then

check

$(vcGlobalQVD)

hope this helps

Where Exists problem

hopkinsc — Thu, 01 Mar 2012 12:15:43 GMT

Hi, they seem to be ok, If i remove the WHERE EXISITS from the second table, it all loads in correctly. So it looks like theres a problem with that command or how i have done it.

Does it look ok?

Re: Where Exists problem

swuehl — Thu, 01 Mar 2012 12:39:22 GMT

Well, is TradingDate part of your second input table at all? If not, this cannot work.

If the field name is different, you might want to look into the two argument version of exists().

Regards,

Stefan

edit: Just saw the field TradingDate, my mistake.

One thought: is TradingDate maybe a TimeStamp, formatted as Date?

Re: Where Exists problem

hopkinsc — Mon, 12 Mar 2012 10:53:02 GMT

Hi, I am still having this problem, The format of both date fields are the same.

This application worked great but has now stopped working for some reason. No changes have been made so i am unsure why it no longer works...

If i remove the Where Exists (TradingDate) from the PosTransactionHeader table, then all of the data loads in.

Confused!!

Any help will be appreciated.