topic Re: Relaod Time in QlikView

Relaod Time

Anonymous — Fri, 21 Nov 2014 02:53:13 GMT

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

Anonymous — Fri, 21 Nov 2014 03:13:29 GMT

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

JonnyPoole — Fri, 21 Nov 2014 03:14:05 GMT

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

(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

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

(ooxml, embedded labels, table is Sheet1)

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

Re: Relaod Time

ToniKautto — Fri, 21 Nov 2014 03:15:00 GMT

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

(ooxml, embedded labels, table is Sheet1)
WHERE Year <= 2014 
  AND Year >= 2012
  AND (Quarter = 'Q3' OR Quarter = 'Q4')
;

Re: Relaod Time

Anonymous — Fri, 21 Nov 2014 03:17:47 GMT

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: Re: Relaod Time

Anonymous — Fri, 21 Nov 2014 03:19:40 GMT

Hey,

I think you may use the attachd for dynamic year calculation.

BR,

Chinna

Re: Re: Relaod Time

ToniKautto — Fri, 21 Nov 2014 03:20:14 GMT

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

Anonymous — Fri, 21 Nov 2014 03:21:19 GMT

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

jagan — Fri, 21 Nov 2014 03:23:39 GMT

Hi,

Try like this

LOAD

Year,

Quarter,

Sales

FROM

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

Replace(1, top, StrCnd(null))

))

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

Replace(1, top, StrCnd(null)) - Fills all empty Year values with previous row values.

Hope this helps you.

Regards,

Jagan.

Re: Relaod Time

Anonymous — Fri, 21 Nov 2014 03:26:30 GMT

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

JonnyPoole — Fri, 21 Nov 2014 03:29:39 GMT

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

Anonymous — Fri, 21 Nov 2014 03:30:00 GMT

Is there any way to write

Max( Year),

Max(Year) -1

inside the Script in where condition?

Re: Relaod Time

jagan — Fri, 21 Nov 2014 03:30:43 GMT

Hi,

Try like this

LOAD

Year,

Quarter,

Sales

FROM

(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

Anonymous — Fri, 21 Nov 2014 03:32:05 GMT

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

JonnyPoole — Fri, 21 Nov 2014 03:36:44 GMT

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

Anonymous — Fri, 21 Nov 2014 03:36:54 GMT

hey,

Check out Jagan's solution is dynamic.

Regards,

Chinna

Re: Relaod Time

JonnyPoole — Fri, 21 Nov 2014 03:40:49 GMT

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

Anonymous — Fri, 21 Nov 2014 03:43:18 GMT

Jagan

could you share me the sample app

I could not execute your script. It is failing from my end

Re: Relaod Time

Anonymous — Fri, 21 Nov 2014 03:47:48 GMT

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

JonnyPoole — Fri, 21 Nov 2014 03:54:27 GMT

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')) ;