topic Re: Filter data when loading in QlikView

Filter data when loading

Thu, 17 Apr 2014 07:57:51 GMT

Hi, I have a SampleData set attached.

In the sample data I have a column called Yearmonth. When I load the data from this data set, I would like to choose the lastest 3 year data. How should I write for the script? Thanks!

Yearmonth

201103

201403

201203

....

Re: Filter data when loading

magavi_framsteg — Thu, 17 Apr 2014 08:15:31 GMT

Hi Jelly.

In pseudo code it would be something like this:

load ...

where Yearmonth > date(addmonths(now(), -36), 'YYYYMM')

You need to tweak the expression to get the full third year to something like:

load ...

where

year(AddMonths(today(), -36)) & 01

Re: Filter data when loading

ariel_klien — Thu, 17 Apr 2014 08:16:03 GMT

Hi,

try this:

LOAD Yearmonth,

[Percent of pass],

Grade

FROM

C:\Users\arielkl\Downloads\SampleData.xlsx

(ooxml, embedded labels, table is Sheet1)

where match(Yearmonth,201103,201403,201203)

Ariel

Re: Filter data when loading

Thu, 17 Apr 2014 08:23:30 GMT

Thanks

If the data set is big enough. I don't know which three years is the latest three year...?

Re: Filter data when loading

Thu, 17 Apr 2014 08:30:49 GMT

Thanks!

If I use the today(), the data just count back from the date of today not the latest day in the dataset....?

Re: Filter data when loading

ariel_klien — Thu, 17 Apr 2014 08:41:03 GMT

So, you can take the Max(Yearmonth) and use the function AddMonths( Max(Yearmonth) ,-36)

Ariel

Re: Filter data when loading

Thu, 17 Apr 2014 11:49:34 GMT

Try to put in Excel and Load the excel sheet in script.

Then, define SET variable and put this variable in where.

Regards

Amol Khochare

Re: Filter data when loading

maxgro — Thu, 17 Apr 2014 17:18:23 GMT

you can use a two step load:

Directory;

// calculate min year month to load

load max(Yearmonth) as MaxYearMonth

FROM

SampleData.xlsx

(ooxml, embedded labels, table is Sheet1);

LET vMax =date(addmonths(date#(Peek('MaxYearMonth'), 'YYYYMM'), -36), 'YYYYMM');

trace $(vMax);

// load from min year month

LOAD Yearmonth,

[Percent of pass],

Grade

FROM

SampleData.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Yearmonth >= $(vMax)

;