topic Re: How to store the data of a table into Monthly and MonthEnd QVD's? in QlikView

How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Wed, 13 Mar 2013 02:00:41 GMT

Hi All,

I want to store transaction data(table contains 3 years data) which is stored in a qlikview table in to Monthly QVD and MonthEnd QVD. Each Monthly QVD should contain entire month QVD(eg: 1st Jan to 31st Jan or 1st Jan to 20th Jan(If the data contains only till 20th Jan)). Each MonthEnd qvd should contain data for End of the month(eg: Jan MonthEnd QVD should contain data only for 31st Jan or it should contain 20th Jan if(If the data contains only till 20th Jan).While storing the qvd(Both Monthly and MonthEnd QVD) it should be prefixed with table name and suffixed with month and Year. We store it in seperate folder to differentiate the same.

Could you please help me out to store the QVD's as described above. It is little urgent!

Thanks,

Attitude

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

sushil353 — Wed, 13 Mar 2013 17:43:43 GMT

Hi,

Please find the attached sample.

You will get idea how to store qvd with month name and year.

HTH

Sushil

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

nicolett_yuri — Wed, 13 Mar 2013 17:47:04 GMT

Largest_Lowest_Date:
LOAD
MonthStart(MIN(DATE)) as Largest_Date,
MonthEnd(MAX(DATE)) as Lowest_Date
RESIDENT Table;

LET Total_Month = round((peek('Largest_Lowest_Date',0)-peek('Largest_Lowest_Date',0))/30);

FOR i=0 to $(Total_Month)-2

LET v_Start = Date(AddMonths(peek('Lowest_Date',0), $(i)), 'DD/MM/YYYY hh:mm:ss');
LET v_Qvd = Date(AddMonths(peek('Lowest_Date',0), $(i)), 'YYYYMM');
LET v_End = Date(MonthEnd(AddMonths(peek('Lowest_Date',0), $(i))), 'DD/MM/YYYY hh:mm:ss');

Qualify *;
T1:
LOAD
*
RESIDENT Table
WHERE
DATE >= '$(v_Start)'
AND DATE <= '$(v_End)';

STORE T1 INTO$(PathQvd)\T1_$(v_Qvd).qvd;

DROPTABLE T1;

NEXT i;

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Wed, 13 Mar 2013 18:00:01 GMT

Thanks for your reply. But this is not what I am looking for!

I have table which contains daily transaction data for 2 years. I want to store these data into Monthly QVD and MonthEnd QVD. Any idea on this?

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Wed, 13 Mar 2013 18:04:55 GMT

Hi Yuri,

I couldn't try it now as I dont have qlikview installed at home. But looking at your script I wonder whether this works to store both Monthly and MonthEnd QVD's. If you could you please explaing me the same. If MonthEnd is not implemented in the above script could you please add the same and send it to me in your convenient time.

Thanks,

Rikab

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

nicolett_yuri — Wed, 13 Mar 2013 18:10:06 GMT

The script is the same, just change the table field "Largest_Lowest_Date" to use MonthEnd Date. And also need to change the column "DATE" in table "T1"

DATE> = '$ (v_Start)'

AND DATE <= '$ (v_End)';

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Wed, 13 Mar 2013 18:18:20 GMT

Sorry I didn't get you! Could you please do the required change in the above script and update me for MonthEnd QVD's. For your information I need to store the Monthly and MonthEnd QVD's using single date field which is available in the transaction table. It contains the date with timestamp!

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Thu, 14 Mar 2013 03:51:27 GMT

Hi Yuri,

Please reply me back it is little urgent! I am not able to make the required change in the script.

Thanks,

Attitude

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

sushil353 — Thu, 14 Mar 2013 08:58:09 GMT

Hello,

i have created a test app for you.

Please find attached.

Please let me know if you have any concern.

HTH

Sushil

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

Thu, 14 Mar 2013 09:08:27 GMT

Hi,

Refer this script You will get idea.

let Monthname=monthname(today()-30);

Load*
FROM

(ooxml, embedded labels, table is Gentlemen, filters(
Transpose(),
Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 2), Select(2, 2)))
));

Regards,

K.J.Nirmal Raj.

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Thu, 14 Mar 2013 09:42:30 GMT

Hi Sushil,

Thanks for your help! I am yet to check your code. I want to store both Monthly and MonthEnd qvd's in below format but in different folder(To differentiate montly and monthend qvd).Could you please do the required change in the script.

QVDNAME.2012_01.QVD Format: QVDNAME.YYYY_MM.QVD

Thanks,
Attitude

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Thu, 14 Mar 2013 10:02:46 GMT

Hi Sushil,

Are you there? Can you please reply back as soon as you can as it is little urgent

Thanks,

Attitude

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Thu, 14 Mar 2013 10:04:51 GMT

Thanks Nirmal I will refer and update

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

Thu, 14 Mar 2013 10:10:59 GMT

Is this working.

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Thu, 14 Mar 2013 10:22:57 GMT

Please give me sometime. I am currently trying what sushil has given to me. It is something I was looking for. I will try yours later and will update you.

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

sushil353 — Thu, 14 Mar 2013 11:43:28 GMT

Hello if you want to store the qvd's in specific folder then change the code as

Initially set a variable for the desired folder path

SET vcQvdPath = I:\Qlikview\Qvds\

then change in the code

STORE [$(monthname)_END] into $(vcQvdPath )T_$(monthname)_END.qvd(qvd);

HTH

Sushil

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

qlikviewforum — Thu, 14 Mar 2013 11:56:23 GMT

I wanted something like below! It was posted earlier!

Hi Sushil,

QVDNAME.2012_01.QVD Format: QVDNAME.YYYY_MM.QVD

Thanks,
Attitude

Re: How to store the data of a table into Monthly and MonthEnd QVD's?

sushil353 — Thu, 14 Mar 2013 12:11:04 GMT

Hi,

i have changed the code,

make sure your folder path should me correct.

Please let me know i you have any concern..

and if your problem is resolved then mark this as right answere and close the thread.

HTH

Sushil