Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
If you try :
let vDateDay = weekday(Date#('$(vDate2)','DD/MM/YYYY'));
It works?
Aurélien
let vDateDay=weekday(Date#($(vDate),'YYYYMMDD'))
let vDateDay2=weekday(Date#($(vDate2 ),'DD/MM/YYYY'))
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.
Hi,
If you try :
let vDateDay = weekday(Date#('$(vDate2)','DD/MM/YYYY'));
It works?
Aurélien
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?
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