topic Re: Date Problem - I can only make it work if hard coded in QlikView

Date Problem - I can only make it work if hard coded

josephinetedesc — Fri, 30 Dec 2016 03:32:58 GMT

Hi all

this is working beautifully BUT

in my Calendar tab I hard code these 2 lines - I would prefer not to!

LET vDateMin = Num(MakeDate(2012,07,01));

LET vDateMax = Num(MakeDate(2016,09,01))

TempCalendar:

LOAD

Date($(vDateMin) + RowNo() - 1) as DateFull,

Year(Date($(vDateMin) + RowNo() - 1)) as Date_YearNum,

Monthname(Date($(vDateMin) + RowNo() - 1)) as Date_MonthNameTemp,

Num(Month(Date($(vDateMin) + RowNo() - 1))) as Date_MonthNum,

Num(Day(Date($(vDateMin) + RowNo() - 1))) as Date_DayNum,

WeekDay(Date($(vDateMin) + RowNo() - 1)) as Date_DayName,

WeekDay(Date($(vDateMin) + RowNo() - 1)) as ISOWeekDay,

WeekEnd(Date($(vDateMin) + RowNo() - 1)) as Date_WeekEnding

AUTOGENERATE 1

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

I have tried changing

LET vDateMax = Num(MakeDate(2016,09,01)) and it fails (see error Message)

Error Message

Field not found - <<=>

TempCalendar:

LOAD

Date(41091 + RowNo() - 1) as DateFull,

Year(Date(41091 + RowNo() - 1)) as Date_YearNum,

Monthname(Date(41091 + RowNo() - 1)) as Date_MonthNameTemp,

Num(Month(Date(41091 + RowNo() - 1))) as Date_MonthNum,

Num(Day(Date(41091 + RowNo() - 1))) as Date_DayNum,

WeekDay(Date(41091 + RowNo() - 1)) as Date_DayName,

WeekDay(Date(41091 + RowNo() - 1)) as ISOWeekDay,

WeekEnd(Date(41091 + RowNo() - 1)) as Date_WeekEnding

AUTOGENERATE 1

WHILE 41091+IterNo()-1 <=

I know that the Max(DateFull) and Min(DateFull) and MaxString(DateFull) and Minstring(DateFull) work when the load has finished ... even following the error message - as shown by the text below

There was a problem with the dates: below is how the date EDT comes into the script 2015-08-26 13:58:00

I have made this: Date(Floor(EDT))) as DateFull

full script for Calendar:

//LET vDateMin = Num(MakeDate(2012,07,01));

//LET vDateMax = Num(MakeDate(2016,08,01));

LET vDateMin = Num(MakeDate(2012,07,01));

LET vDateMax = Num(MakeDate(2016,09,01));

//LET vDateMax = Num(Max(MakeDate(DateFull)));

TempCalendar:

LOAD

Date($(vDateMin) + RowNo() - 1) as DateFull,

Year(Date($(vDateMin) + RowNo() - 1)) as Date_YearNum,

Monthname(Date($(vDateMin) + RowNo() - 1)) as Date_MonthNameTemp,

Num(Month(Date($(vDateMin) + RowNo() - 1))) as Date_MonthNum,

Num(Day(Date($(vDateMin) + RowNo() - 1))) as Date_DayNum,

WeekDay(Date($(vDateMin) + RowNo() - 1)) as Date_DayName,

WeekDay(Date($(vDateMin) + RowNo() - 1)) as ISOWeekDay,

WeekEnd(Date($(vDateMin) + RowNo() - 1)) as Date_WeekEnding

AUTOGENERATE 1

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

join

Fin_Dates_Temp:

LOAD * INLINE

[

Date_MonthNum, FinMonthNum, Quarter

7, 1, 1

8, 2, 1

9, 3, 1

10, 4, 2

11, 5, 2

12, 6, 2

1, 7, 3

2, 8, 3

3, 9, 3

4, 10, 4

5, 11, 4

6, 12, 4

];

Calendar:

LOAD *,

if(FinMonthNum >0 and FinMonthNum<=6,

Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),

(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_Fin,

if(FinMonthNum >0 and FinMonthNum <=6,

Mid(Date_YearNum,3,2) & '/' & Mid((Date_YearNum+1),3,2),

Mid((Date_YearNum-1),3,2) & '/' & Mid(Date_YearNum,3,2)) as Date_FinancialYearShrt2,

'Q' & Quarter & '_' & if(FinMonthNum >0 and FinMonthNum<=6,

Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),

(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_QuartFinYear,

if(ISOWeekDay>=5,'View Saturdays only','Remove Saturdays') as IsWeekEnd

Resident TempCalendar;

DROP Tables TempCalendar

;

Re: Date Problem - I can only make it work if hard coded

Anil_Babu_Samineni — Fri, 30 Dec 2016 04:37:50 GMT

Change it to

LET vDateMin = Min(Date(EDT));

LET vDateMax = Max(Date(EDT));

And then Check in text object how this coming and can you confirm me one thing whether what is the start date and what is the end date?

Re: Date Problem - I can only make it work if hard coded

josephinetedesc — Fri, 30 Dec 2016 04:49:11 GMT

Hi Anil

it still falls over at the same point.

=maxstring(DateFull) & ' Max DateFull-\n ' &

minstring(DateFull)& ' Min DateFull -\n ' &

maxstring(date(EDT))& 'Max date(EDT)' &

minstring(date(EDT))& ' Min Date(EDT) -\n ' &

Re: Date Problem - I can only make it work if hard coded

josephinetedesc — Fri, 30 Dec 2016 05:28:27 GMT

Hi Tamil

I have done a cutdown version and posted it.

Re: Date Problem - I can only make it work if hard coded

tamilarasu — Fri, 30 Dec 2016 05:45:04 GMT

Hi Jo,

Have a look at the attached file.

Re: Date Problem - I can only make it work if hard coded

johnw — Fri, 30 Dec 2016 17:21:48 GMT

Here's example script to build a calendar extremely quickly from the min and max values of a date field. It doesn't require a resident load, a temporary table, or variables.

Calendar:
LOAD *
,date(monthstart(Date),'MMM YYYY') as Month
,date(yearstart(Date),'YYYY') as Year
;
LOAD Min+iterno()-1 as Date
WHILE Min+iterno()-1 <= Max
;
LOAD min(fieldvalue('Date',recno())) as Min
, max(fieldvalue('Date',recno())) as Max
AUTOGENERATE fieldvaluecount('Date')
;

Re: Date Problem - I can only make it work if hard coded

tamilarasu — Sat, 31 Dec 2016 04:40:09 GMT

Hi Jo,

I have attached a solution in the below post yesterday. Kindly check and let us know if any issues. Happy weekend.

Re: Date Problem - I can only make it work if hard coded

josephinetedesc — Tue, 03 Jan 2017 02:19:24 GMT

Hi Tamil

Ok I see what you have done: I will check I am not sure that the financial year is working ... (after all I haven't looked at it since last year

//=======================================================================

MinMaxDate:

Load Min(DateFull) as MinDate,

Max(DateFull) as MaxDate

Resident Part1 Order by DateFull;

Let vDateMin = Num(Monthstart(AddMonths(Peek('MinDate',0,'MinMaxDate'),1)));

Let vDateMax = Num(Monthstart(Peek('MaxDate',0,'MinMaxDate')));

DROP Table MinMaxDate;

//=======================================================================