Scan for existance of a character in uploaded data and display the fields in which the character occurs

[#1]

Here is a tricky one.

I have a number of QVD's with key fields (used in associations) that contain combined keys (fields that have been joined together to form a unique key) separated with a certain character. However, some of the QVD files contain a '/' as separator and some QVD files are using a '|' as separator. Is there a way to read a QVD file and search for the '/' character. If the '/' character is found, the field containing the character should be stored in a table. The character is stored in the data itself and NOT in the field names. However, the field names can be recognized as they start with key_

I already have an application that loops each QVD file given a list.

What would be the best approach to get the result ?

[Miguel_Angel_Baeyens]

Hi,

If you know that there are those fields starting in "Key_" you may loop for them with your code and do

If(Index(Key_FieldName, '/'), 1, 0) AS HasChar

Then do a load of those HasChar fields to store it later in a table.

Is this what you are looking for?

[#3]

I already heard about that function and I guess that would be OK to check for the character on field level. But how do I build a field list from a QVD file ?

Are there any functions that can do that ? You can not perform a LOAD key* FROM qvdfile statement. In addition, how would you loop through the different fields of a table ? Is there a function you can use in scripting like : for each field in table...

I already used for each file in filelist to loop through all the qvd files of a folder matching certain criteria, but I have no idea how to do this for a field list. It would also be nice if I could only load the key_ fields of a QVD file, as I only want to perform the check on key_ fields and not on the 30 or 40 other fields in the QVD file.

[#4]

Hi,

The QVD file begins with a header written in plain text. Take a small QVD file and look at it with Notepad. You need to take the first 100 lines from each file (with command line tools as head or csplit), and search for your fields called key_ .

Alternatively, first do a LOAD * from QVD with "generate logfile" option enabled. The *.qvw.log file will contain a listing of "found fields: A, B, C, KEY_D, ".

If there are man QVD files, processing them manually is not really an option.

-Alex

[Miguel_Angel_Baeyens]

Hi,

You can start with something similar to

Table1:LOAD * INLINE [F1, Key_1A, 1B, 2C, 3D, 4]; STORE Table1 INTO Table1.qvd; FOR i = 0 TO QvdNoOfFields('Table1.qvd') IF Left(QvdFieldName('Table1.qvd', $(i)), 3) = 'Key' THEN KeyFields: LOAD DISTINCT QvdFieldName('Table1.qvd', $(i)) AS KeyField FROM Table1.qvd (qvd); END IFNEXT

and create a variable or a table with all "Key_*" fields to process it later.

Hope this helps