Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

weekday function - can't get the expected value

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.

1 Solution

Accepted Solutions
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

If you  try :

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


It works?


Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

5 Replies
anbu1984
Master III
Master III

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

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

Not applicable
Author

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.

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

If you  try :

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


It works?


Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

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?

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!