topic Re: Loop thru files and concatenate in QlikView

Loop thru files and concatenate

userid128223 — Thu, 11 Aug 2016 21:40:59 GMT

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

Re: Loop thru files and concatenate

petter — Thu, 11 Aug 2016 22:15:17 GMT

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

Re: Loop thru files and concatenate

userid128223 — Fri, 12 Aug 2016 05:15:26 GMT

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

Re: Loop thru files and concatenate

jonathandienst — Fri, 12 Aug 2016 05:27:10 GMT

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;

Re: Loop thru files and concatenate

petter — Fri, 12 Aug 2016 05:34:27 GMT

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