topic Re: Latest 12 weeks of data in script in QlikView

Latest 12 weeks of data in script

Fri, 28 Feb 2014 22:04:20 GMT

Hi,

I have a QVD wich has last 3 years of data with a weekly granularity. However I dont need last 3 year of data and wanted write an expression in script only which will load latest 12 weeks of data everytime I refresh the dashboard.

My script looks like below:

QUALIFY*;
CI:
LOAD

     GLOBAL_BUSINESS_UNIT as GBU,
     [SUB GBU],
     [SUB GBU-PRODUCT_CATEGORY] as [Product Category],
     left(BUS_AREA_CD,2) as [Product Line],
     C_PLTFRM_SUBSET_NM as [Product Code Name],
     C_HP_PROD_HIER_LVL_05_DN as [Prodoct Name],
     PROD_NO as [Product Number],

     FISC_WK_TO_MON_CD as Month,
     Date(FISC_WK_END_DT,'YYYYMMDD') as [Fiscal Week],
     FIS_CAL_WEEK_ID,

     TOTAL_QTY,
     NET_PRICE
FROM

(qvd)

where match (GLOBAL_BUSINESS_UNIT,'IPS','LES') and match(FIS_CAL_WEEK_ID,max(FIS_CAL_WEEK_ID)-12);

Its showing error , how can I get latest 12 weeks in the data. The FIS_CAL_WEEK_ID is a cronological numeric number.

Simky

Re: Latest 12 weeks of data in script

shree909 — Fri, 28 Feb 2014 22:17:17 GMT

Hi, Create a temptable and variable

TempDate:

LOAD

FIS_CAL_WEEK_ID

(qvd)

Max_Week:

Load (max(FIS_CAL_WEEK_ID)-12) AS Max_Week

resident TempDate;

Let v12weekPrior=peek('Max_Week','0','Max_Week');

Drop table TempDate;

Ur actual script

My script looks like below:

QUALIFY*;
CI:
LOAD

where match (GLOBAL_BUSINESS_UNIT,'IPS','LES') and (FIS_CAL_WEEK_ID>='$(v12weekPrior)';

Hope this helps,

if not post a sample qvw file...

Thanks

Re: Latest 12 weeks of data in script

Fri, 28 Feb 2014 22:46:27 GMT

Hi Simky,

12 Weeks of data is roughly 3 months of data.

Table:

Load *

From Source

Where Date >= AddMonths(today(),-3) ;

Thanks

Re: Latest 12 weeks of data in script

Sat, 01 Mar 2014 00:41:40 GMT

It worked. Thanks for the help.