Assembly of QVD files in a Data Model

[lylererger]

Hi friends.

For the first time I collect QVD files in a data model. Therefore it would be desirable to discuss with you methodology of assembly of QVD.

For a start I will tell you about that as qvd are arranged. I pull data from the SQL server. I analyze data storage density and if quantity of lines in the table more than 5 million, I break tabular data by years, if average quantity of lines in a year more than 5 million, I break on months.

Also qvd of such format turn out:

RTableName_2008.qvd - by Year

AAnotherTableName_200901.qvd - by Month

So then i'm trying put all those qvd's put all together:

Sub DoDir (Root)

     For Each Ext In 'qvd' // filetype to search for in current directory

          For Each File In FileList (Root & '\*.' & Ext)

                    Files:

                    Load

                    '$(File)' as Way,

                    FileTime('$(File)') as Time,

                    Replace(Mid('$(File)',Index('$(File)','\', SubStringCount('$(File)','\'))+1),'.$(Ext)','') AS FileName

                    Autogenerate 1;

          Next File

     Next Ext

     For Each Dir In DirList (Root & '\*') // searching in subdirs

          Call DoDir (Dir)

     Next Dir

End Sub

Call DoDir ('..\load'); // Starting point

LET vRows = NoOfRows('Files');

for vIx = 0 to vRows-1

LET vFile = Peek('FileName',vIx,'Files');

LET vDir = Subfield(Peek('Way',vIx,'Files'),'\',4);

LET vHeader = SubField('$(vFile)','_',1);

TRACE Loading $(vDir)_$(vHeader);

$(vDir)_$(vHeader):

LOAD *

FROM $(vDir)\$(vFile).$(Ext)(qvd);

NEXT vIx

On the input i have huge stack of QVD-files in Dirs and SubDirs (about ~ 500).On the output i'm getting huge amount of SYN-keys. So help me with that.

When a certain stack of files booted - to quit FOR - NEXT and to load them together (for example with 200801 on 200901). Only that according to any characteristics they would be separated (a class and a table name). Then to delete the loading table. And back we return to a cycle.

But it is my version especially as I don't know as it to implement... But I imply that so.

Maybe my vision isn't right. Therefore I address to you, is here somebody who in a collection has more correct, fast, simple algorithm, or other methodology of the solution of the similar task.

BR, OM.

[#2]

We can concatenate the all the tables to avoid the SYN Keys.

for vIx = 0 to vRows-1

LET vFile = Peek('FileName',vIx,'Files');

LET vDir = Subfield(Peek('Way',vIx,'Files'),'\',4);

LET vHeader = SubField('$(vFile)','_',1);

TRACE Loading $(vDir)_$(vHeader);

IF(vlx=0) THEN

     $(vDir)_$(vHeader):

     LOAD *

     FROM $(vDir)\$(vFile).$(Ext)(qvd);

     LET vTable = '$(vDir)_$(vHeader)';

ELSE

CONCATENATE ($(vTable ))

     LOAD *

     FROM $(vDir)\$(vFile).$(Ext)(qvd);

ENDIF

NEXT vIx

[lylererger]

Yes but i want something like this:

I've got in Folder:

___________________

ATab1_2008.qvd
ATab1_2009.qvd

...
BTab2_200701.qvd
BTab2_200702.qvd

...

____________________

And i want to concatenate tables with same Class (A) and Names (Tab1) then qualify them. And On the Output want to see the next in data model:

ATab1

and all those fields qualifyied

and

BTab2

the same way.

Thnx, OM.

[#4]

In the FileList you can pass the ClassTableName then you can load all the same table into one table.

For Each File In FileList (Root & '\ATab1*.' & Ext)

[#5]

In the FileList you can pass the ClassTableName then you can load all the same table into one table.

For Each File In FileList (Root & '\ATab1*.' & Ext)

[fernando_tonial]

Hi, You can try just a Load.

 LOAD * FROM *.QVD (qvd);

Best Regards.

Tonial.