topic Load only Last 12 months data in QlikView

Load only Last 12 months data

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below requirement.

I am extracting the data from Oracle database.

I have the script like below

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

Client,

Date,

START TIME,

END TIME;

PROD_QLIK:

SELECT Client,

Date,

START TIME,

END TIME

FROM "USER".PROD_QLIK;

In the above script I have the date format like YYMMDD. From this date field I have to load only last 12 Months data int the extraction layer.

How to add where condition for restricting the last 12 months data.

Thanks in advance.

Re: Load only Last 12 months data

ramasaisaksoft — Mon, 19 Feb 2018 11:34:07 GMT

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

Client,

Date,

Date(date#(START TIME,'YYYY-MM-DD'),'YYYY-MM-DD') as [START TIME]

END TIME;

PROD_QLIK:

SELECT Client,

Date,

START TIME,

END TIME

FROM "USER".PROD_QLIK;

WHERE year(START TIME)=year(now())-1
AND month(START TIME)=month(now())-12
;

Re: Load only Last 12 months data

arvind_patil — Mon, 19 Feb 2018 11:54:57 GMT

Hi Bhavani,

may be like this:

if I assume your date format is DDMMYY .

let vDate = date(addmonths(Today(),-12),'DDMMYY');

LIB CONNECT TO 'Remedy-ODBC64 (John)';

LOAD

Client,

Date,

START TIME,

END TIME;

PROD_QLIK:

SELECT Client,

Date,

START TIME,

END TIME

FROM "USER".PROD_QLIK where Date > $ (vDate) ;

Thanks,

Arvind Patil

Re: Load only Last 12 months data

cjliew — Tue, 15 Dec 2020 04:17:42 GMT

Hi Arvind,

Can you help me to check my script has any problem? It is not working like your?

Let vDate = Date(AddMonths(MonthStart(Today()), -12), 'M/D/YYYY');

[Temp]:
LOAD
"Mandator (Code)",
Date("First Edit Date - Head", 'M/D/YYYY') as "First Edit Date - Head",
"Main Sales Order No.",
"Order Value (Sales Value)"

FROM [lib://Finance/Open Sales Order/ OpenSalesOrder Report (Also not open) 202102.xlsx]
(ooxml, embedded labels, table is page)
Where "First Edit Date - Head" > $(vDate);