Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
STORE Mobilereport_$(vYr_Split_StoreQVD) into Mobilereport_$(vYr_Split_StoreQVD).qvd(qvd);
DROP Table Mobilereport_$(vYr_Split_StoreQVD);
Next i;
DROP TABLES YearData;
Hi Vishal,
go through below Article which will help you to achieve your result.
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.
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
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
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.
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.
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
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
STORE Mobilereport_$(vYr_Split_StoreQVD) into Mobilereport_$(vYr_Split_StoreQVD).qvd(qvd);
DROP Table Mobilereport_$(vYr_Split_StoreQVD);
Next i;
DROP TABLES YearData;
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
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
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
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