topic Re: please help on separate QVD year wise in QlikView

please help on separate QVD year wise

Anonymous — Fri, 05 Dec 2014 20:11:43 GMT

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

Re: please help on separate QVD year wise

oscar_ortiz — Fri, 05 Dec 2014 20:25:30 GMT

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

Re: please help on separate QVD year wise

Anonymous — Fri, 05 Dec 2014 20:58:10 GMT

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

Re: please help on separate QVD year wise

oscar_ortiz — Fri, 05 Dec 2014 21:05:25 GMT

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;

Re: Re: please help on separate QVD year wise

Anonymous — Fri, 05 Dec 2014 21:56:20 GMT

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

Re: Re: Re: please help on separate QVD year wise

oscar_ortiz — Sat, 06 Dec 2014 00:11:41 GMT

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

Re: please help on separate QVD year wise

Anonymous — Sat, 06 Dec 2014 15:57:45 GMT

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

Re: please help on separate QVD year wise

prieper — Sun, 07 Dec 2014 18:51:28 GMT

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

Re: please help on separate QVD year wise

its_anandrjs — Sun, 07 Dec 2014 21:59:51 GMT

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

Re: please help on separate QVD year wise

Anonymous — Mon, 08 Dec 2014 06:25:29 GMT

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

Re: please help on separate QVD year wise

prieper — Mon, 08 Dec 2014 08:27:31 GMT

either you may generate monthwise data, or you load two qvd's (one for each year) and do the selection/filtering on basis monthes during the load.

HTH

Peter

Re: please help on separate QVD year wise

Anonymous — Mon, 08 Dec 2014 12:27:55 GMT

Can any one has solution on below thread

http://community.qlik.com/message/675483#675483

For getting Year wise QVD below script worked .Thanks for all your Solutions.

temp:
load max([Monthstart_Sales]) as period Resident $(Sale_Table);
let vperiodcheck=peek('period',0,'temp');
let vlastyear=year(today())-1;
let vCurrentyear=year(today());

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

TEMP1:
NoConcatenate LOAD * Resident $( Sale_Table)
where [Monthstart_Sales]<YearStart(Today());
store TEMP1 into Sale_previous_$(vlastyear).qvd(qvd);
drop Table TEMP1;
ELSE
TEMP1:
NoConcatenate LOAD * Resident $( Sale_Table)
where [Monthstart_Sales]>=YearStart(Today());
store TEMP1 into Sale_current_$(vCurrentyear).qvd(qvd);
drop Table TEMP1;

Re: please help on separate QVD year wise

Anonymous — Mon, 08 Dec 2014 14:48:23 GMT

How To get Rolling 12 months of data from Accounting Period of each Financial QVD

Any solution on Thread For QVD Generators Year wise QVD Got.

Now need to Load QVD in Finall Application to filter only 12 months of data from present Month