Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 ?
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
Thanks again Miguel,
Youre a real knight in shinning armour today !!!
Thanks
Hi Miguel,
Can I confirm that this will only update once a day ?
Thanks
A