topic Re: An Update load not Refreshing in QlikView

An Update load not Refreshing

Mon, 05 Dec 2011 15:41:37 GMT

Hi Guys

I load in from an excel sheet, called [Data 12]

This is a manual Spreadsheet that is over typed everyday.

So today on a Monday I come in and Reload Qlikview this pulls in all the data into QV from

[Data 12] - this will have all Fridays info.

Date Meat Number

1/1/11 Beef 20

1/1/11 Chicken 10

1/1/11 Beef 17

Throughout the working day on Monday the team will delete and over type the data into

[Data 12],

Date Meat Number

4/1/11 Beef 11

4/1/11 Chicken 5

4/1/11 Beef 20

Is it possible that when I reload tomorrow I can retain the data from Friday and in addition take the data from Monday so I have :

Date Meat Number

1/1/11 Beef 20

1/1/11 Chicken 10

1/1/11 Beef 17

4/1/11 Beef 11

4/1/11 Chicken 5

4/1/11 Beef 20

Currently when I reload I am OVERWRITING the Data from Friday so am just getting

Date Meat Number

4/1/11 Beef 11

4/1/11 Chicken 5

4/1/11 Beef 20

Would appreciate any assistance

Thanks

Re: An Update load not Refreshing

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 16:27:35 GMT

Hi,

Store each day data into a QVD file. This storage can be incremental (only one QVD file with more records each day) or multiple files, one for each day, so you can chose later on which of the QVD files will load.

LET vFileDate = Date(Today(), 'YYYYMMDD');
DailyData:
LOAD * 
FROM Excel.xls
(biff, explicit labels, table is Sheet1$);
STORE DailyData INTO $(vFileDate).qvd (qvd);
DROP TABLE DailyData;
AllData:
LOAD Date,
     Meat,
     Number
FROM *.qvd (qvd);

The first line creates a variable with no special characters to be used as the name of the file. The result will be files named like 20111104.qvd, 20111205.qvd and so on.

The DailyData table loads only data from today, irrespective the changes, and stores into a file like above.

The AllData table loops in the specified folder and loads all QVD files, so you have data from all days. Since all files have the same number and name of fields, all records are concatenated automatically which is very convenient in this case.

Now you can modify the code to fir your needs, load only when not exists, and so. If needed, you can easily create a loop to load only the last month, last year or any given period with i.e.: a For Next loop.

Hope that gives you an idea.

Miguel

Re: An Update load not Refreshing

Mon, 05 Dec 2011 17:01:49 GMT

Hi Miguel

This is brand new to me , I have tested with a sample file , this is my script

LET vFileDate = Date(Today(), 'YYYYMMDD');

DailyData:
LOAD *
FROM
~~(biff, embedded labels, table is Sheet1$);~~

STORE Data INTO $(vFileDate).qvd (qvd);

DROP TABLE DailyData;

AllData:
LOAD Date,
Meat,
Number
FROM *.qvd (qvd);

And these are the errors -- Could you advise if I have done something incorrect ?

Re: An Update load not Refreshing

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 17:10:34 GMT

Hi,

This line was wrong. The STORE needs to get the name of an existing table:

STORE DailyData INTO $(vFileDate).qvd (qvd);

DailyData is a table, now it will work.

Miguel

Re: An Update load not Refreshing

Mon, 05 Dec 2011 17:18:43 GMT

Thanks again Miguel,

Youre a real knight in shinning armour today !!!

Thanks

Re: An Update load not Refreshing

Tue, 06 Dec 2011 09:00:58 GMT

Hi Miguel,

Can I confirm that this will only update once a day ?

Thanks