Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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
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;
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