Filw checker utility in QlikView

[sudeepkm]

Hi All,

One of my new requirement is to create a file checker utility in QlikView that will read multiple xls files and can report which xls files are missing which fields. For example as per the snapshot given below, assume that there are 3 xls files in my data sources folder. first xls file contains 4 fields, the second xls file contains 2 fields (missing Field3 and Field4) and the third xls file is missing Field1.

I need to build a QVW that will output the list of xls files loaded and the fields missing from each files. Appreciate any help here.

Thanks in Advanced..

[salto]

Hello Sudeep,

thes link explains the script code that you could use to check all files in a folder:

http://community.qlik.com/message/381347#381347

Regarding the existence of the fields, write this for every field to load:

if(len(Field1)=0, 'There is no Field1', Field1) as Column1

Hope this helps.

[sudeepkm]

I've a solution for Loading multiple files from same folder but thanks for the link.

My major problem is how to manage and report about those fields that are not present in the source files.

The code len(Field) works if the Field is present but the values are absent. In my case few of the fields in my source files are missing.

i tried a logic to catch the "field not found error" in the script and then create another table to store all the fields available in that source file. but when I load multiple files having different fields missing then QlikView does not respond and hangs. I've to close it from task manager. I think I need some kind of concatenation logic for my errorfiles table so that all those files having missing fields can be arranged in one single table.

goodfiles:

          LOAD 'no field error' as err,[Field1], [Field2], [Field3], [Field4], FileName() as filename

          FROM [$(vDataFolder)$(vFileName)] (biff, embedded labels, header is 1 lines, table is @$(i))  where RowNo()<=1;

if(ScriptError=11) then

errorfiles:

           load * ,FileName() as filename,'field not found error' as err

           FROM [$(vDataFolder)$(vFileName)] (biff, embedded labels, header is 1 lines, table is @$(i))  where  RowNo()<1;

ENDIF