Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading Issue

Hi,

I have to load nearly 100 excel sheets, where the fields are

ProjectHeadCountSkillLevel
ABC3xyz2
cde3
ghik4

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.

--Sindhu.

1 Solution

Accepted Solutions
Colin-Albert

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

     LOAD

           Project,

           HeadCount,

           Skill,

           Level

     FROM ...\ *.xlsx

     set errormode = 1 ;  // normal error reports

View solution in original post

17 Replies
ravic906
Creator III
Creator III

Hola,

Main:

Load *

from path\*.xlsx

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.

Not applicable
Author

Thanks for you reply.

But when I try to do so, in the table viewer the tables are like

main.PNG

Kindly help in this.

Thanks,

Sindhu.

balkumarchandel
Specialist II
Specialist II

Hi Sindhu,

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 -

Test:

LOAD *

FROM

(ooxml, no labels, table is Sheet1)

;

if NoOfFields('Test') > 3 then

drop table Test;

LOAD YrMonth,

     TotalAp,

     left(YrMonth,3)as Month,

    // Month(Month1)as Month,

     Right(YrMonth,2) as Year

FROM

(ooxml, embedded labels, table is Sheet1);

else

drop table Test;

LOAD * INLINE [

Month,MonthNum

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

Note:

in else part skip the load and check for other file.(hope you have logic to load multiple files in using loop.

sushil353
Master II
Master II

Hi,

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.

HTH

sushil

neetha_p
Master
Master

Hi Sindu,

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;

Regards

Neetha 

Not applicable
Author

Hi,

I'm new to qlikview, little confused to write the logic to load multiple files.

Can you please help in this regard.

--Sindhu.

neetha_p
Master
Master

Hi Sindhu,

Have you tried above code i posted

Regards

Neetha

Not applicable
Author

Hi Neetha,

Can you help me in understanding the logic used.

So I'll know what changes I should make to it.

Regards,

Sindhu

Not applicable
Author

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.

--Sindhu