Relaod Time

[Anonymous]

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

[Anonymous]

Hey,

PFA..

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

BR,

Chinna

[JonnyPoole]

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

[ToniKautto]

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

[Anonymous]

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

[Anonymous]

Hey,

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

BR,

Chinna

[ToniKautto]

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

[Anonymous]

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

[jagan]

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.

[Anonymous]

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