Create monthly reports from same file

[parpatra]

Hi,

I am looking out for the best practice to report from a file which is overwritten every month.

Ex-Filename:ABC.xls

Month	Employee	Expense
Jan-10	A	1000
Jan-10	B	2000
Jan-10	C	3000
Jan-10	D	4000
Jan-10	E	5000
Jan-10	F	6000

We recieve Feb,2010 data with the same file name ABC.xls, with a new set of data. The number of rows is expected to be fixed, just the col expense will change month over month.

Now I have tried partial reload option and able to generate reports but still looking out for answers to the following:

1) Is using incremental load a better option?

2)Is there any issues I should keep in mind for scheduling monthly reloads?

[boorgura]

I have been using incremental load and I find no issues with it.

As we store the historic data in QVD, it will be much faster as opposed to another data source.

A logic to looks for the date and then do replace/add might make updating much easier.

[#3]

Hi

Why dont yoou load the xls-file into qlikview and then store it into an qvd-file (and name it with reloady-earmonth). Then you can reload everything from that folder and dont need to use incremental reload.

BR /D

[parpatra]

Thanks Rocky...

I am pretty new to qlikview and can you please help me with some sample examples on incremental load.

[boorgura · Accepted solution]

For the first time - you will have to use the source file and save the data into QVD.

Let say you have Jan data in abc.xls - and you saved it to abc.qvd.

Going forward, you can use a increment script.

Test:

LOAD * from abc.xls

concatenate LOAD * from abc.qvd;

store Test into abc.qvd;

This is a straight load with no logic.

that is you are always adding the xls file to the QVD without checking if the data is already in.

you can have that done as well if you put in some conditions.

I know I gave a quick run. Please feel free to ask for more explanation.

[#6]

Hi parpatra,

we had a similar situation and solved it by renaming the file before loading. In your load script yo can use wild carts, i.e. if you can afford, that the filename are all like ABC_Mon.xls (ABC_Jan.xls, ABC_Feb.xls, ... ). Its much easier and more transparent.

Else: store the xls-data every month into a seperate qvd - file, using above mentioned filenamelogic. This is an application you can call the "QVD-Generator". Then load into your "enduser-application" all the qvd-files. And leave incremental load alone.

Hope you got it.

Roland

[parpatra]

Thanks Rocky...

I tried your logic...but it dint work for me. I am attaching the qvw and xls file. Can you please verify if the logic is correct or not?...

Well..I do not find an option to attach file...!!!

[parpatra]

Thanks Roland...

I do not have the permission to change the filename as ABC_Jan.xls. Also I dint understand the "Wild cards" in script.

Do you have any other suggestions?

[#9]

Hi parpatra,

so you have to use the inc. load. My idea was to work with several files which have similar names like ABD_*.xls. The '*' is a wlid card. You can use it in your script to load from several files in one load.

Ex.:

LOAD Name,
[Account ID],
Target
FROM
C:\example01.xls (biff, embedded labels, table is Sheet1$);

loads only from the file "C:\example01.xls".

-----

Load ...

FROM
C:\example*.xls (biff, embedded labels, table is Sheet1$);

loads every matching filename i.e the file above but also a file named "C.\example02.xls" or "C:\exampletrlala.xls".

Regards

Roland

[parpatra]

Thanks Roland..!!!