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

[JonnyPoole]

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;

[Anonymous]

Is there any way to write

Max( Year),

Max(Year) -1

inside the Script in where condition?

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

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

Hope this helps you.

Regards,

Jagan.

[Anonymous]

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

[JonnyPoole]

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

[Anonymous]

hey,

Check out Jagan's solution is dynamic.

Regards,

Chinna

[JonnyPoole]

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

[Anonymous]

Jagan

could you share me the sample app

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

[Anonymous]

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

[JonnyPoole]

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