please help on separate QVD year wise

[Anonymous]

Hi Please provide solution on to generate multiple QVD on year basis.2013 related data into 2013.qvd and 2014 relates data in 2014.qvd

i.e., Jan-1-2013 to Dec-31-2013 as 2013.qvd and Jan-1-2014 to Dec-31-2014 as 2014.qvd

Tried below script

temp:

load max([Monthstart_Sale]) as period   Resident $(SALES_Table);

let vperiodcheck=peek('period',0,'temp');

let vlastyear=year(today())-1;

//let vCurrentyear= Year(Yearstart( Today()));

LET vCurrentyear = num(Year(today()));

If month($(vperiodcheck))=1

store Sales into sales$(vlastyear).qvd(qvd);

ELSEIF

store Sales into sales_Previous_$(vcurrentyear).qvd(qvd);

EXIT Script;

But unfortunately getting sales$(vlastyear).qvd i.e.,2013.qvd with jan-nov not getting dec data

and not able to getting 2014.qvd

Below is error log:

12/6/2014 12:40:43 AM: 0717  temp:

12/6/2014 12:40:43 AM: 0718  load max([Monthstart_Sale]) as period   Resident Sales

12/6/2014 12:40:43 AM:       1 fields found: period, 1 lines fetched

12/6/2014 12:40:43 AM: 0721  let vperiodcheck=peek('period',0,'temp')

12/6/2014 12:40:43 AM: 0722  let vlastyear=year(today())-1

12/6/2014 12:40:43 AM: 0724  LET vCurrentyear = Num(today())

12/6/2014 12:40:43 AM: 0728  If month(41974)=1

12/6/2014 12:40:43 AM:       Error: Script line error:

12/6/2014 12:40:43 AM: 0729  store Sales into sales_2013.qvd(qvd)

12/6/2014 12:40:43 AM: 0730  ELSEIF

12/6/2014 12:40:43 AM:       Error: Script line error:

12/6/2014 12:40:43 AM: 0732  store Sales into sales_Previous_.qvd(qvd)

12/6/2014 12:40:43 AM: 0736  EXIT Script

12/6/2014 12:40:43 AM:      Execution finished.

please do needful.Thanks

[oscar_ortiz]

I see a couple of issues with the script:

The first is the period check variable.  What are you expecting to see there.  The way you have it coded it seems that you are looking for a numeric period 1-12.  If so you'll need to modify your script accordingly.

The second is the elseif statement, i believe you simply want an else there.  An elseif is looking for another condition to check for.

Good luck

Oscar

[Anonymous]

Oscar as new to Qlik struggling on dates can you help on this to generate year wise qvd

[oscar_ortiz]

Can you provide sample data for your temp table?

This script will complete without errors, but is it what you are looking to accomplish?

Sales:

LOAD * INLINE [

    Monthstart_Sale

    12/01/2014

    11/01/2014

    10/01/2014

    9/01/2014

    8/01/2014

    7/01/2014

    6/01/2014

];

temp:

load

  max( Num(Month([Monthstart_Sale])) ) as period  

Resident Sales;

let vPeriodCheck=peek('period',0,'temp');

let vLastYear = Year(Today())-1;

LET vCurrentYear = Year(Today());

If $(vPeriodCheck)=1 then

  Store Sales into sales$(vLastYear).qvd(qvd);

ELSE

  Store Sales into sales_Previous_$(vCurrentYear).qvd(qvd);

EXIT Script;

[Anonymous]

please find attached sample to reference  created month start,month end to Account period column.

To get year wise qvd and in second step need to rolling of past 12 months of data from current date.

Need to check all qvd and fetch data

[oscar_ortiz · Accepted solution]

You can use this for your initial query in regards to splitting current year and prior year.

I'll come up with a solution for your rolling twelve months shortly.

*** I've updated the attachment with the Rolling 12 fix.  Have a look and let me know.

Good Luck

Oscar

[Anonymous]

I tried to Generate year wise QVD nut not getting exactly.can any one correct the logic which have written problem with the dates

[prieper]

Hi

would solve it with a help-table:

LET vYear = YEAR(TODAY()) -1;

LET vFileName = '.\AnnualTable_For_' & vYear & '.qvd';

PreviousYear:

NOCONCATENATE LOAD * RESIDENT TotalTable WHERE YEAR(Date) = $(vYear);

STORE PreviousYear INTO $(vFileName) (qvd);

DROP TABLE PreviousYear;

HTH Peter

[its_anandrjs]

Hi Swetha,

You can try this way also by load different variables and then load all year tables and store them

YearCount:
LOAD Year as TotYr Resident Source;

LET vYearMin = Peek('TotYr',0,'YearCount');
LET vYearMax = Peek('TotYr',-1,'YearCount');

Drop Table YearCount;

FOR vYear = $(vYearMin) to $(vYearMax)
//////////
NoConcatenate
$(vTableName):
LOAD * Resident Source Where Year = $(vTableName);
STORE $(vTableName) into $(vTableName).qvd;

NEXT

Regards

Anand

[Anonymous]

Thank Anand  for your solution got solution with if else logic,your solution is also working.I'm able to split qvd year basis in qvd generators but to  use year wise qvd in final application to get previous 12 months of data from current date?Any help on this please it will help on great day