So each day you would add you will create a new table with a new date? This will continue for how long? May be explore Incremental load:
I am not really sure what your intent is, may be if you can share some more details, I might be able to offer a better help. To me it seems that today is 5/3/2016, so if we start accumulating stuff today, you will call this Day 1. And then when tomorrow comes, it will become your Day 2 and so on.... Is this what you envision, or are you trying to do something else?
Actually there may be two separate items here; 1st one is comparing two or more set of data with different date and the second one is the incremental load. It is somewhat related to the way I am doing but not 100% linked.
Here is more elaborate information:
I have multiple files and currently each file comprises data for one day (24 hrs data).
For each file, the table contains Time Stamp (let's assume field A), field B, field C and so on so forth.
I'd like to compare hourly data for each date in a chart on top of each other. Since each file has different dates, I had to change the dimension such they only show one day period instead of different days. I used
MakeDate(year([Time Stamp]),month([Time Stamp]),day([Time Stamp])) as day
I'd like to be able to show indicators for each day in the chart, so they have different lines/bars and colors. What I did was by adding additional field that I can use for the dimension. I did by using one of the columns in the records to denote this new filed. Let's say field B is not used, so I added 'Date n' as B for each tab.
I created one tab for each date, so If I have 4 different dates there will be 4 tabs and "n" signifies the date. Each tab loads separate file associated with each date.
The question is: is the a more efficient way to do this?
Second issue is incremental load:
The above data will keep growing, but I just want to limit it to just one more day or two. While the existing data has a complete 24 hours of data, this new data or files may not contain 24 hours, it could be only 2 or 4 hours data and it will be added periodically until 24 hours data is completed.
While the data is growing and I don't have a complete set for 24 hours I need to analyse the new set of data and compare it with the previous records. So the charts could be the comparison of same period with different dates
or a full day but last date only shows partial.
The question was how to load and add only the new records when running the script and loading new files. the existing files are already huge so I don't want to process all available data each time I need to add new files in the directory. It seems the script shown below is quite complex. Can someone explain it in plain English or have a simpler one. I am new to Qlikview.
use for each for read all file
let vDataFolder = '.\buget\';
load * inline [File_Name, Sheet_Name, cod, denumire];
FOR EACH folder IN DirList('$(vDataFolder)'&'*')
FOR EACH subfolder IN DirList('$(folder)'&'\*')
for each vFile in filelist('$(subfolder)\*.xls')
OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];
let vFileName = mid(vFile, index(vFile, '\', -1) + 1);
for iSheet = 0 to NoOfRows('Temp_Tables') -1
let vSheetName = peek('TABLE_NAME', $(iSheet), 'Temp_Tables');
let aa= upper((replace(replace(vSheetName, '$', ''), chr(39), '')));
//if left(('$(aa)'),8)='TARGETS_' and right(('$(aa)'),7)='_CLIENT' then
FROM [$(vFile)] (biff, embedded labels, table is [$(vSheetName)]);
subfield('$(folder)','\',-1) as Folder_Name,
subfield('$(subfolder)','\',-1) as Subfolder_Name,
'$(vFileName)' as File_Name,
'$(vSheetName)' as Sheet_Name
drop table aaa;
DROP TABLE Temp_Tables;