topic Partial reload of data in QlikView

Partial reload of data

Wed, 03 Mar 2010 14:56:52 GMT

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.

Partial reload of data

Wed, 03 Mar 2010 15:22:21 GMT

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

Partial reload of data

Wed, 03 Mar 2010 15:28:47 GMT

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.

Partial reload of data

Thu, 04 Mar 2010 09:01:20 GMT

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

Partial reload of data

Thu, 04 Mar 2010 09:15:59 GMT

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.

Partial reload of data

rwunderlich — Thu, 04 Mar 2010 09:21:51 GMT

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

-Rob

Partial reload of data

Wed, 23 Feb 2011 11:35:06 GMT

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$);