please help on load 12 months of data from qvd

[Anonymous]

Hi friends I have a year wise  qvd  I.e.,2012.qvd,2013.qvd,2014.qvd in final application need to load 12 months of data from current date.e.g:let today()->12/10/2014 need to first check 2014.qvd which has 11 months of data .step2:In 2013.qvd need to load only 1 month of data .here my main problem is before loading into Qlik the qvd are to be evaluate condition.In qvd I have  Monthstart  and Month end 'mm/dd/yyyy'    

Can any one help on solution    asap since last 2 day's struggling with problem.                  

[its_anandrjs]

Hi Swetha,

A solution for you for last 12 months qvd loading as i already provide you to generate year wise qvd. Then load current and last year qvd and check for the last 12 months records only for this see the load script here.

LET vCurrentYear = Year(Today())&'.qvd';

LET vPreviousYear = (Year(Today()) - 1)&'.qvd';

LET vToday = Date( Today()-1);

LET vLast12Monnth = AddMonths(Date( Today()-1), -12);

FinalTable:

LOAD *,'Current Year' as TableFlag

FROM $(vCurrentYear)

(qvd) Where

Date >= AddMonths(Date( Today()-1), -12,'DD/MM/YYYY') And Date  <= Date( Today()-1,'DD/MM/YYYY');

Concatenate

LOAD *,'Previous Year' as TableFlag

FROM $(vPreviousYear)

(qvd) Where

Date >= AddMonths(Date( Today()-1), -12,'DD/MM/YYYY') And Date  <= Date( Today()-1,'DD/MM/YYYY');

Note:- Here Date is date field you can use your date field there and load this way it is dynamically also.

And Maintain correct date format also in this expression

Date >= AddMonths(Date( Today()-1), -12,'DD/MM/YYYY') And Date  <= Date( Today()-1,'DD/MM/YYYY');

Let me know.

Regards

Anand

[mrybalko]

Hello

I suggest to generate all necessary months and then load qvd in optimized mode using where exists condition. Example is in attachment

[Anonymous]

Thanx for all your valuable solutions Really helped a  lot on Date Functions.Actually I'm thinking is when ever a previous qvd Load there it self filter particular month of data.For this spent almost 3 days .Once again thanks to all

[its_anandrjs]

Thanks and if you need exactly 12 months back data from one day back then write with below expression with the variables.

FinalTable:

LOAD *,'Current Year' as TableFlag

FROM $(vCurrentYear)

(qvd) Where

Date >= AddMonths(Date( Today()-1), -12,'DD/MM/YYYY') And Date  <= Date( Today()-1,'DD/MM/YYYY');

Concatenate(FinalTable)

LOAD *,'Previous Year' as TableFlag

FROM $(vPreviousYear)

(qvd) Where

Date >= AddMonths(Date( Today()-1), -12,'DD/MM/YYYY') And Date  <= Date( Today()-1,'DD/MM/YYYY');

This give data from 09/12/2013 to 09/12/2014 one day back.

Regards

Anand