topic Re: weekday function - can't get the expected value in QlikView

weekday function - can't get the expected value

Fri, 07 Nov 2014 14:13:01 GMT

Hi All,

Probably a very simple issue here. Dates always confuse me within Qlikview!

I'm using the following chunk of script to allow me to loop through and run some specific date ranged load statements:

YEARS:

load * inline [

YEAR

2009

2010

2011

2012

2013

];

let NoOFYears = NoOfRows('YEARS')-1;

for YearCounter = 0 to $(NoOFYears)

      let vYear = Peek('YEAR',$(YearCounter),'YEARS');

     // set date ranges

       let vMinDate = num(makedate($(vYear),01,01));

       let vMaxDate = num(makedate($(vYear),12,31));

     // create temp cal

       TempCal:

       load

            date($(vMinDate) + rowNo() - 1) as TempDate

       AutoGenerate

            $(vMaxDate) - $(vMinDate) + 1;

     let NoOfDates = NoOfRows('TempCal')-1;

     // loop through the temp cal

            for DateCounter = 0 to $(NoOfDates)

          // get the date on this record

            let vDate = date(Peek('TempDate',$(DateCounter),'TempCal'),'YYYYMMDD');

            let vDate2 = date(Peek('TempDate',$(DateCounter),'TempCal'),'DD/MM/YYYY');

            let vDateDay = weekday($(vDate));

However, the weekday() function on the last line is giving me the incorrect value. Looking at the date 01 Jan 2009 If I use the variable vDate (as above, 20090101) vDateDay is set to Tue. If I use the variable vDate2 (same information, just formatted differently - 01/01/2009) then I get a vDateDay of Sat. 1st Jan 2009 was a Thursday though.....

Haven't changed anything in the standard set statements: SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Thanks,

Rory.

Re: weekday function - can't get the expected value

anbu1984 — Fri, 07 Nov 2014 14:22:16 GMT

let vDateDay=weekday(Date#($(vDate),'YYYYMMDD'))

let vDateDay2=weekday(Date#($(vDate2 ),'DD/MM/YYYY'))

Re: weekday function - can't get the expected value

Fri, 07 Nov 2014 14:40:20 GMT

Thanks Anbu,

Have modified my script as follows:

// get the date on this record

let vDate = date(Peek('TempDate',$(DateCounter),'TempCal'),'YYYYMMDD');

let vDate2 = date(Peek('TempDate',$(DateCounter),'TempCal'),'DD/MM/YYYY');

let vDateDay = weekday(Date#($(vDate2),'DD/MM/YYYY'));

however, when running debug I'm getting a <NULL> for vDateDay

Thanks,

Rory.

Re: weekday function - can't get the expected value

Aurelien_Martinez — Fri, 07 Nov 2014 15:49:04 GMT

Hi,

If you try :

let vDateDay = weekday(Date#('$(vDate2)','DD/MM/YYYY'));

It works?

Aurélien

Re: weekday function - can't get the expected value

Fri, 07 Nov 2014 15:51:49 GMT

Thanks, that worked like a charm!

Not sure I understand the logic behind that though? I would enclosing the $(vDate2) in single quotes would just use the string $(vDate2). Or does qlikview then still execute the dollar sign expansion?

Re: weekday function - can't get the expected value

Aurelien_Martinez — Fri, 07 Nov 2014 15:57:14 GMT

Without quotes, QlikView execute this : let vDateDay = weekday(Date#(12/12/2012,'DD/MM/YYYY'));

12/12/2012 is not a string

With quotes, QV execute : let vDateDay = weekday(Date#('12/12/2012','DD/MM/YYYY'));

and '12/12/2012' is a string

Aurélien