Problems with calendar after concatenation

Thu, 09 Jun 2011 10:05:27 GMT

Hi all,

I am a brand new user of QlikView.

I have two tables with a date field and created a master calendar.

When I then load the script, I do not encounter any problems.

But when I concatenate the two tables, I have a problem with my varMaxDate which returns NULL.

I checked the dates in the two tables and there are no NULL values in the date fields ...

Any suggestions to resolve this ??

Davy

Script:

Sales:

LOAD

...,

F1FADT as Date,

...;

FROM [Database.Table1]

ORDER BY F1FADT ASC;

Concatenate(Sales)

LOAD

...,

KACRED as Date,

...;

FROM [Database.Table2]

ORDER BY KACRED ASC;

let varMinDate = num(peek('Date', 0, 'Sales'));

let varMaxDate = num(peek('Date', -1, 'Sales'));

let varToday = num(today());

let var0 = date(0);

TempCalendar:

LOAD

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

$(varMinDate) + rowno() -1 as Tempdate

AUTOGENERATE $(varMaxDate) - $(varMinDate)+1;

Re: Problems with calendar after concatenation

erichshiino — Thu, 09 Jun 2011 11:29:48 GMT

I would change your script a little bit. I think the peek('Date',-1, etc... would not get your max date...

Sales:
LOAD
     ...,
     F1FADT as Date,
     ...;
FROM [Database.Table1]
ORDER BY F1FADT ASC;
Concatenate(Sales)
LOAD
     ...,
     KACRED as Date,
     ...;
FROM [Database.Table2]
ORDER BY KACRED ASC;
maxdate:
load max(Date) as maxDate resident Sales;
let varMaxDate = num(peek('maxDate'));
drop table maxdate;
mindate:
load min(Date) as minDate resident Sales;
let varMinDate = num(peek('minDate'));
drop table mindate;
let varToday = num(today());
let var0 = date(0);
TempCalendar:
LOAD
     $(varMinDate) + rowno() -1 as Num,
     $(varMinDate) + rowno() -1 as Tempdate
AUTOGENERATE $(varMaxDate) - $(varMinDate)+1;

Hope this helps,

Erich

topic Problems with calendar after concatenation in QlikView

Problems with calendar after concatenation

Re: Problems with calendar after concatenation