topic Re: Cumulated data in QVD in QlikView

Cumulated data in QVD

Thu, 03 Sep 2015 16:39:31 GMT

Hello,

I have the following challenge and I hope that Qlikview community exprets would help me.

The database that I connec show me some data every day. I'm showing you an example, the number of registers are always the same but some fileds may change.

The field date_inform is the current day. This field is not in the database, i have to create it.

What I need is to generate a QVD file capable to add the registers generated in the database everyday, adding them one below the other. I need to create a file whith the historical data.

The sheet "TOTAL" it`s what I need in a QVD file.

You can check it in the attached field.

Thank you!!

Re: Cumulated data in QVD

Thu, 03 Sep 2015 16:49:24 GMT

Data:

LOAD

code,

res,

date,

other,

date_inform

FROM YourQvdStore.qvd (qvd);

Concatenate(Data)

LOAD

code,

res,

date,

other,

Today() as date_inform

FROM YourDatabaseTable;

STORE Data into YourQvdStore.qvd(qvd);

DROP Table Data;

Re: Cumulated data in QVD

Thu, 03 Sep 2015 16:59:23 GMT

what you will need to do is

create an itial qvd

once the intial qvd is created, when the applciation is run each day, you will need to

open the qvd

read the day's data from the database

concetenact to the qvd

store the qvd

In essence each day you open the qvd, add the days information and store it sot the qvd will build each day

Re: Cumulated data in QVD

Thu, 03 Sep 2015 17:07:13 GMT

Yes.

Or store different day's data in different qvd files(Data_02.02.2015.qvd,Data_03.02.2015.qvd):

Data:

LOAD

code,

res,

date,

other,

Today() as date_inform

FROM YourDatabaseTable;

LET vToday = Today();

Store Data into Data_$(vToday).qvd(qvd);

You may load all data like this:

LOAD

code,

res,

date,

other,

date_inform

FROM Data_*.qvd(qvd);

Or load only one year:

LOAD

code,

res,

date,

other,

date_inform

FROM Data_*2014.qvd(qvd);

etc.

Re: Cumulated data in QVD

Thu, 03 Sep 2015 17:40:41 GMT

Can you give an example?

Re: Cumulated data in QVD

maxgro — Thu, 03 Sep 2015 17:47:05 GMT

something like

Directory;

IF len(FileSize('db.qvd')) > 0 then

db: load * from db.qvd (qvd);

ENDIF;

db:

LOAD code,

res,

date,

other,

Timestamp(now()) as date_inform

FROM EX_QV.xls (biff, embedded labels, table is TOTAL$);

STORE db into db.qvd (qvd);

replace the excel load with the db load

and the now/timestamp function with today/date

Re: Cumulated data in QVD

Thu, 03 Sep 2015 17:52:01 GMT

$(vTableName):
LOAD * FROM
< dat source>);

store $(vTableName) into <qvd name>.qvd(qvd);

let vListQVDDetailsNotExist = date(QvdCreateTime((<qvdname'.qvd'),M/D/Y);
let vToday = Date(Today());
if $(vListQVDDetailsNotExist) <> $(vToday) then
let vTable name = <name>
$(vTableName):
//

LOAD * FROM
<qvd nae>.qvd(qvd);
Concatenate(tablename)
LOAD *, Date(Today()) as TranDate;

SELECT *
FROM<daily source of data>;

// DailyQVD:
//
store $(vTableName) into <qvd name>.qvd(qvd);

ENDIF;

Re: Cumulated data in QVD

Fri, 04 Sep 2015 05:24:19 GMT

Today I will try all the instructiones you have done me . I keep you informed. Thank you!!