This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
I have to load nearly 100 excel sheets, where the fields are
The problem is few excel sheets doesn't contain the column HeadCount, so I dont want to load those sheets into QV.
There should be logic to check whether HeadCount field exists or not.
How can I handle this case?
Thanks in advance.
Remember to reset the errormode to 1 after the load command so any other errors are reported and not ignored.
set errormode = 0 ; // disable error reports
FROM ...\ *.xlsx
set errormode = 1 ; // normal error reports
View solution in original post
Now, you will find a synthetic key between tables with all fields and tables less fields i.e., you will have Main and Main-1 tables in table viewer. Drop the table with minimum fields.
Thanks for you reply.
But when I try to do so, in the table viewer the tables are like
Kindly help in this.
In your script you can write logic to check if your file have all the fields or not for ex normally your table have 3 fields (including headcount column) then you can load the excel file example -
(ooxml, no labels, table is Sheet1)
if NoOfFields('Test') > 3 then
drop table Test;
// Month(Month1)as Month,
Right(YrMonth,2) as Year
(ooxml, embedded labels, table is Sheet1);
LOAD * INLINE [
in else part skip the load and check for other file.(hope you have logic to load multiple files in using loop.
If your no of column in all xl sheet is fixed.. let's say it is 4 in above case.
then simply load your excel table as temp.
Then check the no of fields in loaded table
let vFieldCount = noOfFields(temp_table);
now based on the value of vFieldCount you can load the data or else drop the temp table.
Try below code in script:
ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx]; XlsTables: SQLTables; DISCONNECT; LET vRows = NoOfRows('XlsTables'); FOR i = 0 TO $(vRows)-1 LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1); LET vFieldCount = noOfFields(TABLE_NAME); if $(vFieldCount) > 3 then $(vSheetName): LOAD * FROM [ExcelFile.xlsx] (ooxml, embedded labels, table is '$(vSheetName)'); NEXT i DROP TABLE XlsTables;
I'm new to qlikview, little confused to write the logic to load multiple files.
Can you please help in this regard.
Have you tried above code i posted
Can you help me in understanding the logic used.
So I'll know what changes I should make to it.
There are total 11 fields in my excel sheet, where as few sheets has only 10 fields.
All those 10 field sheets I don't want to load into my report.