20 Replies Latest reply: Nov 20, 2014 10:57 PM by jagan mohan rao appala RSS

    Relaod Time

    John Smith

      Hi,

       

      I am attaching the Sample app and go through it and

      I need to have only 2014,2013 years data discarding other years data to reduce the reload time.

      Suggestions on the same

        • Re: Relaod Time
          chinna katiki

          Hey,

           

          PFA..

           

          One query: in you source sheet do you get the year in the same way as rows merged?

          BR,

          Chinna

            • Re: Relaod Time
              John Smith

              Hi Chinna,

              Thnaks for the reply

              1) Merge is for convenience factor not in real time data

              2) I need the quarters (2012 Q4, 2012 Q3) as well

               

              could you suigget me on this

                • Re: Relaod Time
                  chinna katiki

                  Hey,

                  Do you need the above quarters as static or your scenario will be always some thing like..

                   

                  current & Last year data + last 2 quarters of before last year?

                  BR,

                  Chinna

                  • Re: Relaod Time
                    jagan mohan rao appala

                    Hi,

                     

                    Try like this

                     

                     

                    LOAD

                         Year,

                         Quarter,

                         Sales

                    FROM

                    [C:\temp\NEw.xlsx]

                    (ooxml, embedded labels, table is Sheet1, filters(

                    Replace(1, top, StrCnd(null))

                    ))

                    where match(Year, Year(Today()), Year(Today()) - 1) OR

                    (Year = Year(Today()) - 2 AND Match(Quarter, 'Q3', 'Q4'));


                    Hope this helps you.


                    Regards,

                    Jagan.

                • Re: Relaod Time
                  Jonathan Poole

                  Hi -

                   

                  Just add a WHERE clause to the LOAD. To filter for multiple values on the load the match() function is useful. Modify the script to the following:

                   

                  LOAD Year,

                       Quarter,

                       Sales

                  FROM

                  [C:\temp\NEw.xlsx]

                  (ooxml, embedded labels, table is Sheet1)

                  where match(Year,2013,2014);

                   

                  BUT.. i noticed this excel source has merged cells for year, making the load for year a little problematic

                   

                  Capture.PNG.png

                   

                  I resolved it by calculating the Year from the quarter, otherwise qlikview was only loading one quarter per year with the above script.

                   

                  LOAD left(Quarter,4) as Year,

                       Quarter,

                       Sales

                  FROM

                  [C:\temp\NEw.xlsx]

                  (ooxml, embedded labels, table is Sheet1)

                  where match(left(Quarter,4),2013,2014);

                    • Re: Re: Relaod Time
                      Toni Kautto

                      For a cleaner script I would suggest that you fill the blank lines with a filter operation. In the example below empty cells are replaced with the value from above cell, with the filters() defintion.

                       

                      LOAD Year,
                          Quarter,
                          Sales
                      FROM
                      NEw.xlsx
                      (ooxml, embedded labels, table is Sheet1, filters(Replace(1, top, StrCnd(null))
                      ));
                      
                    • Re: Relaod Time
                      Toni Kautto

                      Generally I would advise that you load all data into QlikView if it all fits in memory. Without all data you will not be able to do business discovery on all your data, so in theory you might miss out on interesting facts and details by excluding data already during reload. Instead you can limit the data in your application presentation.

                       

                      There is no way to apply a WHERE clause limiting the data load from file. That means that all data from the file is loaded to QlikView. You can add a WHERE clause in the LOAD statement to limit the number of rows that you want to keep in QlikView. The process time that you gain by that is the time and effort going in to putting the data in QlikView memory.

                       

                      In the LOAD statement you can add a WHERE clause after the source definition. The WHERE clause is a set of logical comparison deciding if rows are kept or not. For example like this;

                       

                      LOAD Year, 
                           Quarter, 
                           Sales
                      FROM
                      [C:\Users\..\Desktop\NEw.xlsx]
                      (ooxml, embedded labels, table is Sheet1)
                      WHERE Year <= 2014 
                        AND Year >= 2012
                        AND (Quarter = 'Q3' OR Quarter = 'Q4')
                      ;
                      
                      • Re: Relaod Time
                        John Smith

                        Thanks to all

                         

                        1) the merge is not there for convenience I made it

                        2) the years and Quarters we could not hard cord directly

                        • Re: Relaod Time
                          Jonathan Poole

                          Consider levering ODBC off Excel if you would like to retrieve only filtered records (rather than all and filter in qlik)

                           

                          Ex below and attached.

                           

                           

                          ODBC CONNECT TO [Test;DBQ=C:\Temp\Year.xlsx];

                           

                          LOAD Year,

                              Quarter,

                              Sales;

                          SQL SELECT *

                          FROM `C:\Temp\Year.xlsx`.Year

                          where Year=2014 or Year=2013;

                            • Re: Relaod Time
                              John Smith

                              Thanks Jonathan

                               

                              we cant hardcode the values of 2014 and 2013 values directly

                              it should be dynamic based on max and max -1

                              could you suggest on this

                                • Re: Relaod Time
                                  Jonathan Poole

                                  voila...

                                   

                                  ODBC CONNECT TO [Test;DBQ=C:\Temp\Year.xlsx];

                                   

                                  //get max year from spreadsheet

                                  Years:

                                  LOAD max(Year) as Year;

                                  SQL SELECT Year

                                  FROM `C:\Temp\Year.xlsx`.Year;

                                   

                                  //save the row retrieved above into a qlik variable

                                  let vMaxYear=peek('Year',0,'Years');

                                   

                                  drop table Years;

                                   

                                  //pull all the data and filter for years leveraging the qlik variable defined above

                                  Data:

                                  LOAD Year,

                                      Quarter,

                                      Sales;

                                  SQL SELECT *

                                  FROM `C:\Temp\Year.xlsx`.Year

                                  where Year=$(vMaxYear) or Year=($(vMaxYear)-1);

                                    • Re: Relaod Time
                                      Jonathan Poole

                                      Almost forgot  ,  ff the spreadsheets are coming with merged cells for the Year column, then to address via SQL you can still calculate the years of the quarter field with a slight update (bolded below):

                                       

                                      --------------

                                       

                                       

                                       

                                      ODBC CONNECT TO [Test;DBQ=C:\Temp\Year.xlsx];

                                       

                                       

                                      Years:

                                      LOAD max(Year) as Year;

                                      SQL SELECT Year

                                      FROM `C:\Temp\Year.xlsx`.Year;

                                       

                                       

                                      let vMaxYear=peek('Year',0,'Years');

                                       

                                       

                                      drop table Years;

                                       

                                       

                                      Data:

                                      LOAD Year,

                                          Quarter,

                                          Sales;

                                      SQL SELECT left(Quarter,4) as Year,Quarter,Sales

                                      FROM `C:\Temp\Year.xlsx`.Year

                                      where Year=$(vMaxYear) or Year=($(vMaxYear)-1);

                                        • Re: Relaod Time
                                          John Smith

                                          Thanks Jonathan for your time and approach

                                          I pleased with the approach, but still thee is a small lag at Quarters part!! ( need Q4,Q3 of 2012 as well along with 2014,2013 years)

                                          and data is not merged in Excel

                                            • Re: Re: Relaod Time
                                              Jonathan Poole

                                              Updated with filters , missing Year values etc.. and attached

                                              ------------------

                                               

                                              ODBC CONNECT TO [Test;DBQ=C:\Temp\Year.xlsx];

                                               

                                               

                                              Years:

                                              LOAD max(Year) as Year;

                                              SQL SELECT Year

                                              FROM `C:\Temp\Year.xlsx`.Year;

                                               

                                               

                                              let vMaxYear=peek('Year',0,'Years');

                                               

                                               

                                              drop table Years;

                                               

                                               

                                              Data:

                                              LOAD Year,

                                                  Quarter,

                                                  Sales;

                                              SQL SELECT left(Quarter,4) as Year,Quarter,Sales

                                              FROM `C:\Temp\Year.xlsx`.Year

                                              where left(Quarter,4)=$(vMaxYear) or left(Quarter,4)=($(vMaxYear)-1) or (left(Quarter,4)=($(vMaxYear)-2) and (right(Quarter,1)='4' or right(Quarter,1)='3'))  ;

                                        • Re: Relaod Time
                                          chinna katiki

                                          hey,

                                           

                                          Check out Jagan's solution is dynamic.

                                           

                                          Regards,

                                          Chinna

                                      • Re: Relaod Time
                                        John Smith

                                        Is there any way to write

                                        Max( Year),

                                        Max(Year) -1

                                        inside the Script in where condition?