Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

An Update load not Refreshing

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

A

1 Solution

Accepted Solutions

Re: An Update load not Refreshing

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

5 Replies

Re: An Update load not Refreshing

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

Not applicable

Re: An Update load not Refreshing

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 ?

errors.bmp

Re: An Update load not Refreshing

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

Not applicable

Re: An Update load not Refreshing

Thanks again Miguel,

Youre a real knight in shinning armour today !!!

Thanks

Not applicable

Re: An Update load not Refreshing

Hi Miguel,

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

Thanks

A