Loop thru files and concatenate

[userid128223]

Hi Everyone

I have qvd that has list of filenames.

Fileloadlist.qvd

File1

File2

File3

I have source.qvd which was generated from privious file loads.

Question: what will be the script to loop thru all the files in FileLoadlist.qvd and append it to Source.qvd.

Thanks

[petter · Accepted solution]

It can look something like this:

Files:
LOAD * FROM FileLoadlist.qvd (qvd);

Data:
LOAD * FROM source.qvd (qvd);

n = NoOfRows('Files');
FOR i = 1 TO n
  fn = FieldValue('FileName',i);    // Presuming FileName is the name of the field/column in the FileLoadlist.qvd
  CONCATENATE LOAD * FROM $(fn) (qvd);
NEXT

STORE Data INTO source.qvd (qvd);

[userid128223]

Thanks peter, i will try it out. another modification on how do you have handle file name of different type to different qvd.

example: loop thru 3 type of files below.

Fileloadlist.qvd

File1_gst.txt

File2_pst.txt

File3_hst.txt

File4_gst.txt

Append to source file for each.  (File1_gst.txt & File4_gst.txt will get appended to gst_source.qvd)

source files

gst_source.qvd

pst_source.qvd

hst_source.qvd

[jonathandienst]

Something like this:

gst:

LOAD * FROM gst_source.qvd (qvd); 

pst:

Noconatenate

LOAD * FROM pst_source.qvd (qvd); 

hst:

Noconatenate

LOAD * FROM hst_source.qvd (qvd); 

Data: 

LOAD * FROM source.qvd (qvd); 

FOR i = 0 TO NoOfRows('Files') - 1

  Let fn = Peek('FileName', i, 'Data');    // Presuming FileName is the name of the field/column in the FileLoadlist.qvd 

  Let target = TextBetween(fn, '_', '.');

  CONCATENATE ($(target))

  LOAD * FROM $(fn) (qvd); 

NEXT 

STORE gst INTO gst_source.qvd (qvd); 

STORE pst INTO pst_source.qvd (qvd); 

STORE hst INTO hst_source.qvd (qvd); 

DROP Table gst, pst, hst;

[petter]

Yes - you will have to extend and modify the code according to your needs. Handling different file types might be one of them. By doing a check on the extension of the filename you can determine whether this is a qvd, txt or xlsx file and handle that dynamically.

For instance:

          ext = SubField( fn , '.' , -1 );    //  - 1 as the third parameter is valid and will pick the first field from the

                                                        //   right. The Qlik Sense documentation is incorrect stating that this parameter

                                                        //    has to be positive... it doesn't luckly

Will pick out the extension part of a filename.

In the load you could then write:

     CONCATENATE LOAD * FROM $(fn) ($(ext));  // This works for QVD and TXT  but XLSX need more params