topic Re: How to create monthly QVD ? in QlikView

How to create monthly QVD ?

Tue, 21 Jun 2016 07:04:53 GMT

I have a qvd file that i need to create from monthly qvd like " 201601.qvd "

How can i do this ?

Re: How to create monthly QVD ?

tamilarasu — Tue, 21 Jun 2016 07:11:12 GMT

You can try like below,

Let vDate= Date(Today(),'YYYYMM');

MyQVD:

Load...

From qvd;

STORE MyQVD INTO $(vDate).qvd;

Re: How to create monthly QVD ?

sushil353 — Tue, 21 Jun 2016 07:11:46 GMT

let vYear = 2016;

let vMonth = 1;

for I=0 to 11

let vQVDFileName = $(vYear)&$(vMonth);

Store TableName into $(vQVDFileName).qvd(qvd);

next I ;

HTH

Sushil

Re: How to create monthly QVD ?

Tue, 21 Jun 2016 07:12:11 GMT

I need to create it from the Date Column in the Qvd file which is called Date.

Re: How to create monthly QVD ?

tamilarasu — Tue, 21 Jun 2016 07:13:37 GMT

What is the date format for the column "Date".?

Re: How to create monthly QVD ?

Tue, 21 Jun 2016 07:14:41 GMT

Its numbers, but my TimeStampformat in the qvw is " YYYY-MM-DD "

Re: How to create monthly QVD ?

tamilarasu — Tue, 21 Jun 2016 07:17:28 GMT

Could you share some example qvd.? It would be easy for me to help further.

Re: How to create monthly QVD ?

qlikview979 — Tue, 21 Jun 2016 07:20:02 GMT

Hi Akintosh,

I hope this is help full to you.

T1:

LOAD Date,

Day(Date) as Date1,

Product,

Sales

FROM

(ooxml, embedded labels, table is Sheet1);

let Vm=NoOfRows('T1');

for i=0 to $(Vm)-1;

LET Vmonth=Month(Peek('Date',$(i),'T1'));

LET Vday=Peek('Date1',$(i),'T1');

STORE T1 into D:\W-905\Personal\DAILY QVDS\Month\$(Vmonth).qvd (qvd);

//////STORE T1 into D:\W-905\Personal\DAILY QVDS\Month\New folder\$(Vmonth)_$(Vday).qvd (qvd);

NEXT i;

Note:- If you uncomment the Second store command you will get Daily Qvd also.

Here My Date format is like "MM/DD/YYYY". instead of my date format give your date format.

Regards,

Mahesh

Re: How to create monthly QVD ?

Tue, 21 Jun 2016 07:22:02 GMT

And some other columns,

Re: How to create monthly QVD ?

Tue, 21 Jun 2016 07:23:39 GMT

Hello Mahesh, thank you for the answer.

The file source is a QVD file and has a Date,Year,Month and some other columns,

and the output should be YEARMONTH.qvd for example: 201601.qvd

Re: How to create monthly QVD ?

qlikview979 — Tue, 21 Jun 2016 07:23:50 GMT

Hi,

Can you share your files.

Regards,

Mahesh

Re: How to create monthly QVD ?

Tue, 21 Jun 2016 07:29:21 GMT

I cannot im sorry.

I just need to create monthly qvd from the date column in the file.

The output of the qvd should be like 201601.qvd 201602.qvd etc.. and up to Max Date ( Month) in the qvd.

Re: How to create monthly QVD ?

qlikview979 — Tue, 21 Jun 2016 07:37:51 GMT

Hi This is help full

Load your QVD then copy the Bold Script may be it will work.

T1:

LOAD Date,

Date1,

Product,

Sales

FROM

(qvd);

let Vm=NoOfRows('T1');

for i=0 to $(Vm)-1;

LET Vmonth=Date(Peek('Date',$(i),'T1'),'YYYYMM');

LET Vday=Peek('Date1',$(i),'T1');

STORE T1 into $(Vmonth).qvd (qvd);

NEXT i;

Note:- check both QVD names is same.like 201601.qvd]

Re: How to create monthly QVD ?

qlikview979 — Tue, 21 Jun 2016 07:40:43 GMT

Hi Akintosh,

Try With My QVD SCript You will get correct output.

After load your QVD copy this

let Vm=NoOfRows('T1'); //////Give your table name////

for i=0 to $(Vm)-1;

LET Vmonth=Date(Peek('Date',$(i),'T1'),'YYYYMM');

LET Vday=Peek('Date1',$(i),'T1');

STORE T1 into $(Vmonth).qvd (qvd); /////give your path///

NEXT i;

Regards,

Mahesh

Re: How to create monthly QVD ?

Tue, 21 Jun 2016 07:41:49 GMT

I will, but what is " Date1 " ?

Re: How to create monthly QVD ?

qlikview979 — Tue, 21 Jun 2016 07:44:55 GMT

Hi,

Thats my one of the field name i used that for "Daily QVD's" Creation.

Re: How to create monthly QVD ?

tamilarasu — Tue, 21 Jun 2016 07:46:41 GMT

Not tested. But try this,

MyQVD:
Load * From yourQVDFile;

Max:
Load Max(Date) as MaxDate,
Min(Date) as MinDate,
Min(Num(Month(Date))) as MinMonth,
Max(Num(Month(Date))) as MaxMonth
Resident MyQVD;

Let vMinDate = Date(Peek('MinDate',0,'Max'),'YYYYMM');
Let vMaxDate = Date(Peek('MaxDate',0,'Max'),'YYYYMM');
Let vMinMonth = Date(Peek('MinMonth',0,'Max'),'YYYYMM');
Let vMaxMonth = Date(Peek('MaxMonth',0,'Max'),'YYYYMM');

DROP Table Max, MyQVD;

FOR year = Year($(vMinDate)) to Year($(vMaxDate))
FOR month = $(vMinMonth) to $(vMaxMonth)
Let vDate = $(year) & Left('0' & $(month) ,2);

MyTable:
LOAD *
FROM
yourQVDFile (qvd)
where Year(Date) = $(year) and MONTH(Date) = $(month);
STORE MyTable INTO $(vDate).qvd;

NEXT
NEXT

Re: How to create monthly QVD ?

qlikview979 — Tue, 21 Jun 2016 07:50:47 GMT

Hi Nag Bro,

Can you check once I think my script will work

T1:

LOAD Date,

Date1,

Product,

Sales

FROM

(qvd);

let Vm=NoOfRows('T1');

for i=0 to $(Vm)-1;

LET Vmonth=Date(Peek('Date',$(i),'T1'),'YYYYMM');

LET Vday=Peek('Date1',$(i),'T1');

STORE T1 into $(Vmonth).qvd (qvd);

NEXT i;

Regards,

Mahesh

Re: How to create monthly QVD ?

Tue, 21 Jun 2016 07:55:17 GMT

Hello Mahesh,

The OutPut of qvd should be with max year of the Date column and every month of what is in the Date Column

So :

201601.qvd

201602.qvd

201603.qvd

. Not just Month.

Re: How to create monthly QVD ?

qlikview979 — Tue, 21 Jun 2016 07:59:56 GMT

Hi ,

Please check this is my source like three files.