5 Replies Latest reply: May 4, 2016 12:02 PM by Fikri Shihab RSS

    Comparing different period in the charts

    Fikri Shihab

      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

          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
              Fikri Shihab

              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

                  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
                      Fikri Shihab

                      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. 

                • Re: Comparing different period in the charts
                  florentina dogaru

                  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;

                              next

                     

                              DROP TABLE Temp_Tables;

                          next;

                      next;

                  next;

                  set errormode=1;