Load only Last 12 months data

[bhavvibudagam]

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.

[ramasaisaksoft]

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
;

[arvind_patil · Accepted solution]

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

[cjliew]

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