topic Create calendar using table field in QlikView

Create calendar using table field

Tue, 27 Jul 2010 18:10:30 GMT

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 TL;-#.##0,00 TL';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Oca;?ub;Mar;Nis;May;Haz;Tem;A?u;Eyl;Eki;Kas;Ara';

SET DayNames='Pzt;Sal;Çar;Per;Cum;Cmt;Paz';

SAMPLEDATA:

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SourceParagon;Data Source=dvdb7;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WCORR896ZB9;Use Encryption for Data=False;Tag with column collation when possible=False];

SQL SELECT "dschrg_ts"

FROM SourceParagon.dbo."TPM300_PAT_VISIT";

SIRALAMA:

LOAD "dschrg_ts" RESIDENT SAMPLEDATA ORDER BY "dschrg_ts" ASC;

LET varMinDate = Num(Peek("dschrg_ts", 0, 'SIRALAMA'));

LET varMaxDate = Num(Peek("dschrg_ts", -1, 'SIRALAMA'));

LET vToday = Num(today());

//*************** Geçici Takvim ***************

TempCalendar:

LOAD

$(varMinDate) + rowno() - 1 AS Num,

date($(varMinDate) + rowno() - 1) AS TempDateAUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

//*************** Ana Takvim ***************

MasterCalendar:

LOAD TempDate as TARIH2,

week(TempDate) AS Week,

year(TempDate) AS Year,

month(TempDate) AS Month,

day(TempDate) AS Day,

weekday(TempDate) AS WeekDay,

'Q' & Ceil(Month(TempDate)/3) AS Quarter,

date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

week(TempDate)&'-'&Year("dschrg_ts") AS WeekYear,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate Asc;

DROP TABLE TempCalendar;

DROP TABLE SIRALAMA;

/* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week".

// Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundry.

LET vToday=today(1);

Ranges:

// Use the "evaluate()" function to execute the input function text and convert it to actual date values.

LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd

;

/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.

LOAD * INLINE [

(delimiter is ';')

;

LEFT JOIN (Ranges) IntervalMatch (TARIH2) LOAD RangeStart, RangeEnd RESIDENT Ranges;

// Because we did a JOIN, we may drop the the Start/End fields.

DROP FIELDS RangeStart, RangeEnd;

I modified the above script to select the "dschrg_ts" field from my table to create the min and max date variables. When I run this script, the load command in the TempCalendar has an error.

Can someone look at this and tell me how to fix it so my calendar is dependent on the table field values?

Thanks

glen

Create calendar using table field

Tue, 27 Jul 2010 20:06:43 GMT

Check values for varMinDate and varMaxDate variables. May be they don't have a value.

Create calendar using table field

Tue, 27 Jul 2010 23:49:55 GMT

I had a problem jus tlike, it took ages to find it.

For me, the issue was this:

When I loaded a field from the database without aliasing it in the load script, it loads the field name in ALL CAPS. So, when I was referencing the field to get the date value, it doesn't find the field.

So, where you're doing this:

LET varMinDate = Num(Peek("dschrg_ts", 0, 'SIRALAMA'));

LET varMaxDate = Num(Peek("dschrg_ts", -1, 'SIRALAMA'));

try this:

LET varMinDate = Num(Peek("DSCHRG_TS", 0, 'SIRALAMA'));

LET varMaxDate = Num(Peek("DSCHRG_TS", -1, 'SIRALAMA'));

Create calendar using table field

Wed, 28 Jul 2010 01:03:39 GMT

The following is my latest script. It still errors out at the TemCalendar creation. There is no value for the varMinDate.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 TL;-#.##0,00 TL';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Oca;?ub;Mar;Nis;May;Haz;Tem;A?u;Eyl;Eki;Kas;Ara';

SET DayNames='Pzt;Sal;Çar;Per;***;Cmt;Paz';

DataCalendar:

Load

"dschrg_ts";

SQL SELECT *

FROM SourceParagon.dbo."TPM300_PAT_VISIT";

//LOAD * INLINE [

// OrderDate

//01.11.2000

//04.02.2001

//09.01.2003

//15.12.2012

//04.11.2008

//01.01.2010]

//ORDER BY DataCalendar Asc;;

LET varMinDate = Num(Peek("DSCHRG_TS", 0, DataCalendar));

LET varMaxDate = Num(Peek("DSCHRG_TS", -1, DataCalendar));

LET vToday = Num(today());

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(varMinDate)+IterNo()-1 AS Num,

Date($(varMinDate)+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS OrderDate,

week(TempDate) AS Week,

year(TempDate) AS Year,

month(TempDate) AS Month,

day(TempDate) AS Day,

weekday(TempDate) AS WeekDay,

'Q' & Ceil(Month(TempDate)/3) AS Quarter,

date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

week(TempDate)&'-'&Year(TempDate) AS WeekYear,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate Asc;

DROP TABLE TempCalendar;

Thanks

Glen