topic Re: Comparing different period in the charts in QlikView

Comparing different period in the charts

Anonymous — Tue, 03 May 2016 19:01:46 GMT

I have multiple files with different dates and time indicated by Time Stamp field. I'd like to assign each period (e.g. day) so that I can select the values (e.g. specific day) in a new field.

I used one of the unused fields in the table columns (lets say column B) to indicate the date/day and created separate tab for each day.

So tab 1 for day 1 will have a statement something like

LOAD *,

A,B,C,D

'Day 1' as B,

MakeDate(year([Time Stamp]),month([Time Stamp]),day([Time Stamp])) as day

FROM

SOURCE

and Tab 2 is :

LOAD *,

A,B,C,D

'Day 2' as B,

MakeDate(year([Time Stamp]),month([Time Stamp]),day([Time Stamp])) as day

FROM

SOURCE

Is there a better way to assign a new field without using one of the column names in the table?

Given the file will be loaded incrementally with the new records in new files, what is the efficient way to incrementally load the new files only with the new data?

Any hint will be helpful and appreciated.

Re: Comparing different period in the charts

sunny_talwar — Tue, 03 May 2016 20:53:43 GMT

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:

http://www.quickintelligence.co.uk/qlikview-incremental-load/‌

Incremental Load in QlikView – Part1 – Learn QlikView

Re: Comparing different period in the charts

Anonymous — Tue, 03 May 2016 21:40:17 GMT

Thanks, Will have a try.

BTW, this is not expected to continue for long time. I have an existing data (e.g. 5 days worth of data) and would retrieve one or two more days to compare.

How about the multiple tabs? is there more efficient way to assign different day (value) for each file?

Re: Comparing different period in the charts

sunny_talwar — Tue, 03 May 2016 23:34:59 GMT

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?

Re: Comparing different period in the charts

florentina_doga — Wed, 04 May 2016 10:07:52 GMT

use for each for read all file

let vDataFolder = '.\buget\';

target:

load * inline [File_Name, Sheet_Name, cod, denumire];

set errormode=0;

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;"];

Temp_Tables:

sqltables;

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

aaa:

LOAD

FROM [$(vFile)] (biff, embedded labels, table is [$(vSheetName)]);

//eNDIF;

Concatenate (target)

LOAD

subfield('$(folder)','\',-1) as Folder_Name,

subfield('$(subfolder)','\',-1) as Subfolder_Name,

'$(vFileName)' as File_Name,

'$(vSheetName)' as Sheet_Name

resident aaa;

drop table aaa;

DROP TABLE Temp_Tables;

next;

set errormode=1;

Re: Comparing different period in the charts

Anonymous — Wed, 04 May 2016 16:02:02 GMT

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.