Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
johnsmithqv
New Contributor III

Relaod Time

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

20 Replies
chinnakatikisg
Valued Contributor

Re: Relaod Time

Hey,

PFA..

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

BR,

Chinna

Employee
Employee

Re: Relaod Time

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

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

(ooxml, embedded labels, table is Sheet1)

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

Employee
Employee

Re: Relaod Time

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

;

johnsmithqv
New Contributor III

Re: Relaod Time

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

chinnakatikisg
Valued Contributor

Re: Re: Relaod Time

Hey,

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

BR,

Chinna

Employee
Employee

Re: Re: Relaod Time

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

));

johnsmithqv
New Contributor III

Re: Relaod Time

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

MVP
MVP

Re: Relaod Time

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.

chinnakatikisg
Valued Contributor

Re: Relaod Time

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

Community Browser