topic Re: Exists in QlikView

Exists

brindlogcool — Sun, 08 Jul 2012 21:41:19 GMT

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.

Re: Exists

Oleg_Troyansky — Sun, 08 Jul 2012 21:50:44 GMT

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.

Re: Exists

brindlogcool — Sun, 08 Jul 2012 22:11:04 GMT

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.

Re: Exists

nagaiank — Sun, 08 Jul 2012 23:00:45 GMT

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.

Re: Exists

Oleg_Troyansky — Mon, 09 Jul 2012 01:48:01 GMT

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?

Re: Exists

brindlogcool — Mon, 09 Jul 2012 02:00:15 GMT

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.

Re: Exists

nagaiank — Mon, 09 Jul 2012 02:41:36 GMT

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.

Re: Exists

nagaiank — Mon, 09 Jul 2012 02:58:13 GMT

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

Re: Exists

brindlogcool — Mon, 09 Jul 2012 03:24:58 GMT

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.