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.                  

[jagan]

Hi,

Try like this

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

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

Temp:

LOAD

*

FROM $(vCurrentYear) (qvd);

Concatenate(Temp)

LOAD

*

FROM $(vPreviousYear) (qvd);

Data:

NoConatenate

LOAD

*

RESIDENT Temp

WHERE DateFieldName > AddYears(Today(), -1);

DROP TABLE Temp;

Note: In above script replace DateFieldName with your actual field name.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Hi Jagan,thanx for your solution but not expecting this.I was expecting to load Current Year QVD first  which has 11 months and need to have condition to check 2013.qvd and only 1 month(Dec-2013)data it has to load .

I dont need to load all QVD Concatenate data and at finall not to apply filter.

temp:

load max([Monthstart_OGIS0O]) as period   Resident [Rolling];

let vperiodcheck=peek('period',-12,'temp');

if num(month($(vperiodcheck)))=1 then;

Load current qvd<----11 months

check

Load 2013.qvd only<----1 month

Like above condition is it possible please help on this ASAP

[jagan · Accepted solution]

Hi,

Then try like this

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

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

Data:

LOAD

*

FROM $(vCurrentYear) (qvd)

WHERE DateFieldName <= MonthEnd (Today(), -1);

Concatenate(Data)

LOAD

*

FROM $(vPreviousYear) (qvd)

WHERE DateFieldName >= MonthStart (Today(), -12);

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Jagan,tried below code to load current Period QVD 2014 and based on this it need to roll 12 months it also check 2013 qvd and pick 1 month data.but my filter is not working exactly can you suggest on this

LET vCurrentYear = Year(Today());

LET vPreviousYear = (Year(Today()) -1);

let vmonthnum=num(month(today()));

let vstartperiod=monthstart(AddMonths(today(),-num(month(today()))));

let vendperiod=AddMonths(today(),-((13-num(month(Today())))));

If $(vmonthnum)=1 then;

set vcheck6='abc';

data:

NoConcatenate

LOAD *

FROM

Sales_[current_2014.qvd]

(qvd)

Where date(Monthstart_OGIS0O)<=MonthEnd(Today()-1);

ELSE

set vcheck7='new';

data:

NoConcatenate

LOAD *

FROM

Sales_current_2014.qvd]

(qvd)

Where date(Monthstart_OGIS0O)<=$(vstartperiod);

Concatenate(data)

LOAD *

FROM

Sales_2013.qvd]

(qvd)

Where date(Monthstart_OGIS0O)>=$(vstartperiod);

[Anonymous]

Here in Above script my current qvd logic is not working 2013 qvd whole data is loading any help on this

[prieper]

Have made the experience that it is faster to load annual tables in optimized mode and then to filter for the required monthes, than to load the file(s) with a filter-mode in the load.

HTH Peter

[Anonymous]

peter can you elaborate on this.requirement is always load 12 months from current need to logic in such a way first it should check current qvd and to load all months present in it.Suppose if let current date is<--04/1/2015

need to get 2015.qvd 4 months and 2014.qvd<--8 months from previous.Any help on this will you suggest on above script .Currently able lo load present 2014.qvd(11 months)in it,but 2013.qvd not able to load 1 month

[prieper]

test - reply seem to be corrupt.

[prieper]

Please create variable like:

LET sDateFrom = ADDMONTHS(TODAY(), -12);

and then filter your data accordingly:

FilteredData:

LOAD * RESIDENT .... WHERE MyDate >= $(sDateFrom);

HTH Peter