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

[qlikviewforum]

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

[sushil353]

Hi,

Please find the attached sample.

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

HTH

Sushil

[nicolett_yuri]

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;

[qlikviewforum]

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?

[qlikviewforum]

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

[nicolett_yuri]

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)';

'

[qlikviewforum]

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!

[qlikviewforum]

Hi Yuri,

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

Thanks,

Attitude

[sushil353]

Hello,

i have created a test app for you.

Please find attached.

Please let me know if you have any concern.

HTH

Sushil

[#10]

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.