Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

hopkinsc
Not applicable

Where Exists problem

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

					
				
			
			
				
			
			
				
5 Replies
jagan
Not applicable

Where Exists problem

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.

SunilChauhan
Not applicable

Where Exists problem

use below

IF FileTime '$(cFileName)' >= 0

or

if not load then

check

$(vcGlobalQVD)

hope this helps

hopkinsc
Not applicable

Where Exists problem

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?

swuehl
Not applicable

Re: Where Exists problem

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?

hopkinsc
Not applicable

Re: Where Exists problem

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.