Exists

[brindlogcool]

Hi,

I was using the below statement to find the holiday.

If ((WeekDay(TempDate)='Sat' or WeekDay(TempDate)='Sun'),1,If(Exists(Holidays,TempDate),2,3)) as Holiday.

In One QVW file it is working fine and in the other QVW file it is not throwing error but not considering the holidays. is there any additional settings has to be changed.

[Oleg_Troyansky]

Shouldn't be ... just double check that the field Holidays is loaded in the second QVW prior to this statement, and that all the field names are spelled exactly the same.

[brindlogcool]

Thanks for your response, I have spent my whole day but could find any issue with the Spelling or loading but the if statement is not working.

Please find the script i have used.

TempCalendar: 

LOAD

Date($(vDateMin) + RowNo() - 1) AS TempDate 

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Holiday:
NoConcatenate
LOAD Date(DT) as Holidays,
FROM
[C;\SLS_CALN.xls]
(biff, embedded labels, table is [Sheet 1$]) Where FLG='Y';

Calendar: 
LOAD
Date(TempDate) AS TempDate,
WeekDay(TempDate) AS DayName, 
If((WeekDay(TempDate)='Sat' or WeekDay(TempDate)='Sun'),1,If(Exists(Holidays,TempDate),2,3)) as Hday,
Day(TempDate) AS DayOfMonth, 
Month(TempDate) AS MonthName, 
'Q' & Ceil(Month(TempDate)/3) AS Quarter, 
Year(TempDate) AS Year, 
MonthName(TempDate) as MonthAndYear, 
MonthStart(TempDate) as MonthStart, 
MonthEnd(TempDate) as MonthEnd 
RESIDENT TempCalendar ;

Please let me know if i miss something here.

[nagaiank]

I loaded the following version of your script and it worked correctly without any error.

(Note: I added values for the variables vMinDate and vMaxDate and also used an Excel spreadsheet of my own in order to reload..

The corrections I made include the following:

- The pathname of the Excel sheet, (Use C: instead of C;)

- Use Lookup function instead of Exists)

Let vDateMin = '07/01/2012';

Let vDateMax = '07/31/2012';

TempCalendar:

LOAD

Date('$(vDateMin)' + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE '$(vDateMin)'+IterNo()-1<= '$(vDateMax)';

Holiday:

NoConcatenate

LOAD Date(DT) as Holidays

FROM

[SLS_CALN.xlsx]

(ooxml, embedded labels, table is Sheet1);

Calendar: 

LOAD

Date(TempDate) AS TempDate,

WeekDay(TempDate) AS DayName, 

//If((WeekDay(TempDate)='Sat' or WeekDay(TempDate)='Sun'),1,If(Exists(Holidays,TempDate),2,3)) as Hday,

If((WeekDay(TempDate)='Sat' or WeekDay(TempDate)='Sun'),1,If(Lookup('Holidays','Holidays',TempDate,'Holiday')=TempDate,2,3)) as Hday,

Day(TempDate) AS DayOfMonth, 

Month(TempDate) AS MonthName, 

'Q' & Ceil(Month(TempDate)/3) AS Quarter, 

Year(TempDate) AS Year, 

MonthName(TempDate) as MonthAndYear, 

MonthStart(TempDate) as MonthStart, 

MonthEnd(TempDate) as MonthEnd 

RESIDENT TempCalendar ;

Hope this Helps.

[Oleg_Troyansky]

I wouldn't go as far as replacing exists() with a lookup(). The correction of the file path should help getting the load right. If you haven't been getting errors on this load, your Error Mode must be set to 0, which is very dangrous in the development phase - you can easily overlook problems like this one. I recommend setting Error Mode to 1, to get informed about any critical errors.

In addition, I'd recommend troubleshooting  your data:

- create a list box with the field TempDate and examine the format and the contents of the field.

- do the same with the field Holidays - is it loaded from Excel as expected? What is the date format? Is it a Date field or perhaps a String or a Number?

[brindlogcool]

Thanks for your response.But it is not working.I think i need to check the date formats.I am completely lost bcs i am not getting nether error nor result.That to it is working in one qlikview file and not working in other qlikview file.

[nagaiank]

I have attached the qvw file and the Excel file of the working application. You may use it, if it helps you fix your date formats.

[nagaiank]

As suggested by Oleg, the application works with the Exists function also.

[brindlogcool]

Thanks Oleg and Krishnamoorthy for your response.There is some issue in passing the Dynamic Start and End Date for the calendar. I have checked your QVW file it is working fine with lookup and the Exists function.

Thanks Oleg the error mode i have set to 1.