Partial reload of data

[#1]

Hi all. I am fresh to QlikView so i have to ask you some things.

I have created a calendar that can be dinamicaly generated based on a variable that contains the number of years in advance. My question is how can i reaload only the calendar not all the information in the file again.

Thank you,

Cristian.

[#2]

Hi,

Just add the "REPLACE" the command before the load statement of the Calendar script. And dont forget to pass the year value in the script.

ex:

REPLACE

LOAD * from table_name where Calendar year = $(variable);

For multiple years you can also go with a FOR loop in the load statement.

Note : If the report is scheduled in the Publisher, ensure the option "Enable Partial Reload" is checked in it.else partial reload will not be executed.

Regards,

Santhosh

[#3]

Hi.

This is the Script:

Where should i put replace ?

LET vYears = 12*VMonthsUp;

LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), vYears)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:
LOAD
TempDate AS OrderDate,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear
RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Regards,

Cristian.

[#4]

As mentioned by you previously,For example if you want to load data from the table for the year 2005,

Here the script goes like this,

LET vYears = 12*VMonthsUp;

LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), vYears)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar: // enable partial reload option in the publisher when scheduling
REPLACE LOAD
TempDate AS OrderDate,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear
RESIDENT TempCalendar WHERE Year(TempDate) = year(today()-5) ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

// Note : ensure correct value is given in the WHERE clause during partial reload.If u want more that one year data use > and < operators in the WHERE clause.

Hope this will be useful for you.All the best.

Regards,

Santhosh

[#5]

Hi Santhosh.

Isnt't it possible to reload the calendar "on the fly" ? Because the calendar is not related to any other data in the qlikview file. It is built from scratch. I am thinking about using this calendar for a WhatIF tool where the user just adds years to the calendar but without having to reaload the whole data again. I am not using a publisher for this.

Regards,

Cristian.

[rwunderlich]

Note that REPLACE is only effective if you invoke Partial Reload (File, Partial Reload).

-Rob

[#7]

Hi, I've found an unpleasant error in QlikView Desktop 64bit 10 IR during a demo with a client.

This script , if invoked with v 9 Sr5, manage correctly FILE>PARTIAL RELOAD ( i.e. DOESN'T try to instantiate OLEDB connection )

If invoked wth V10 IR , gives an error, trying to instantiate OLEDB connection anyway ( you find my credentials masked with **** , they are real during the test ...)

Is it a known bug planned to be fixed in 10.SR2?

Best regards,

Fabrizio.

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=****;Data Source=****;Extended Properties=""] (XPassword is CBLOJXFMObbGGaD);

data:

LOAD * INLINE [

Product, Amount, ANNO_COMPETENZA

A, 100, 2011

B, 200, 2011

]

;

ObjectList:

SELECT * from MyTableObjList;

DatiEpidemiologici:

replace LOAD Sesso,

SubTotale,

Causa,

[NUMERO DI DECESSI],

[TASSO GREZZO],

[TASSO STANDARDIZZATO],

[INTERVALLO DI CONFIDENZA]

FROM

[.\DatiEpidemiologici.xls]

(biff, embedded labels, table is DatiEpidemiologici$);

Cause_Di_Morte:

REPLACE

LOAD CDM_SESSO,

CDM_DESC,

CDM_VALORE

FROM

[.\DatiCausaMorte.xls]

(biff, embedded labels, table is Foglio1$);