Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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.

Tags (2)
1 Solution

Accepted Solutions

Re: Loading Issue

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
Contributor III

Re: Loading Issue

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

Re: Loading Issue

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
Valued Contributor II

Re: Loading Issue

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
Honored Contributor II

Re: Loading Issue

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
Honored Contributor

Re: Loading Issue

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

Re: Loading Issue

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
Honored Contributor

Re: Loading Issue

Hi Sindhu,

Have you tried above code i posted

Regards

Neetha

Not applicable

Re: Loading Issue

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

Re: Loading Issue

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