Need to read multiple qvds in the single script,after minor changes have to save them in the same path with same name??

[vishalgoud]

Have a requirement which i have to directly implement on production it self, Which is giving me ache.

I have almost 20 historical qvds of same name expect the date attached to it.

ex :    Mobilereport_20160101.qvd (YYYYMMDD) ,

          Mobilereport_20160201.qvd.................................  till  Mobilereport_20170801.qvd 

Now in my extraction app  i have to read these qvds ( from Mobilereport_20160701 to Mobilereport_20170801)  and add few columns to them and then have to store them with the same name and in the same path from where they picked. (overwrite on the same qvds with same name).

Notes : all fields and their names are same in all the qvds.

Path is like - E:\Development\GEN\Old Qvds

Please help me with the script to achive this. as it is bit urgent

Many Thanks.

[mdmukramali]

Hi Vishal,

go through below Article which will help you to achieve your result.

Year Wise Qvd creation from Source Data

[vishalgoud]

Experts Please help me, am alone in my team and no one here to help,

was able to proceed till reading the all qvds not specific date range , but facing the problem while storing them in the same path with the same name getting complete script failure.

[mdmukramali]

Hi Vishal,

i tested this script and it's working.

have a look on it.

YearData: // Loading the Month and Year to store

LOAD

DISTINCT

DATE(Date#(SubField(FileBaseName(),'_',2),'YYYYMMDD'),'YYYYMMDD')    AS YrMonth

From (qvd);

    let cnt=FieldValueCount('YrMonth');

    FOR i =1  to cnt;

    LET vYr_Split_StoreQVD = FieldValue('YrMonth',i);

NoConcatenate

Mobilereport_$(vYr_Split_StoreQVD):

LOAD *

//Here you can add new fields  //

From (qvd);

STORE Mobilereport_$(vYr_Split_StoreQVD) into Mobilereport_$(vYr_Split_StoreQVD).qvd(qvd);

DROP Table Mobilereport_$(vYr_Split_StoreQVD);

Next i;

DROP TABLES YearData;

/*

find the attached Application.

still if you have any issue then please share with us script and at least one QVD file.

[vishalgoud]

Thanks a lot Mukram,

you are awesome, perfectly worked for me, But only thing is with your script we read all the qvds with the name like Mobilereport_*  from that specific path..

as per my requirement i have to pick qvds from 20160801 to 20170801. (only 12 qvds out of 20)

I can read all them and resave them after adding the new columns but considering their huge size server will not allow me to do as each qvd is 7GB around.

Please see if you can include that as well. Thanks again.

[mdmukramali · Accepted solution]

Hi,

Add the where condition like below:

//Here you go:

YearData: // Loading the Month and Year to store

LOAD

DISTINCT

DATE(Date#(SubField(FileBaseName(),'_',2),'YYYYMMDD'),'YYYYMMDD')    AS YrMonth

From (qvd)

Where SubField(FileBaseName(),'_',2) >=20160801 and  SubField(FileBaseName(),'_',2) <=20170801

;

    let cnt=FieldValueCount('YrMonth');

    FOR i =1  to cnt;

    LET vYr_Split_StoreQVD = FieldValue('YrMonth',i);

NoConcatenate

Mobilereport_$(vYr_Split_StoreQVD):

LOAD *

//Here you can add new fields  //

From (qvd);

STORE Mobilereport_$(vYr_Split_StoreQVD) into Mobilereport_$(vYr_Split_StoreQVD).qvd(qvd);

DROP Table Mobilereport_$(vYr_Split_StoreQVD);

Next i;

DROP TABLES YearData;

[vishalgoud]

Hi Mohammed,

Sorry for opening the thread again.

as our logic taking the extensions as well ,its working perfectly for Mobilereport_20170801 named qvds and also reading the other qvds with Mobilereport_SUMM_20170801. so which is giving failing because mis match in the column names...

so now we have to restrict the logic to consider the qvds with the same structure from starting to ending..

like Mobilereport_20170801,Mobilereport_20170901,Mobilereport_20171001 and not to consider Mobilereport_SUMM_2010801...

can you please see if we can do that as well, as our folder contains all together.. Thanks

[mdmukramali]

Hi,

You can add another condition in where clause,

you can check the Length of the filename , if it's 21 then load it.

YearData: // Loading the Month and Year to store

LOAD

DISTINCT

DATE(Date#(SubField(FileBaseName(),'_',2),'YYYYMMDD'),'YYYYMMDD')    AS YrMonth

From (qvd)

Where

len(FileBaseName())=21 and

SubField(FileBaseName(),'_',2) >=20160801 and  SubField(FileBaseName(),'_',2) <=20170801

;

    let cnt=FieldValueCount('YrMonth');

    FOR i =1  to cnt;

    LET vYr_Split_StoreQVD = FieldValue('YrMonth',i);

NoConcatenate

Mobilereport_$(vYr_Split_StoreQVD):

LOAD *

//Here you can add new fields  //

From (qvd);

STORE Mobilereport_$(vYr_Split_StoreQVD) into Mobilereport_$(vYr_Split_StoreQVD).qvd(qvd);

DROP Table Mobilereport_$(vYr_Split_StoreQVD);

Next i;

DROP TABLES YearData;

Thanks,

Mohammed Mukram