topic Re: Loading Issue in QlikView

Loading Issue

Thu, 19 Feb 2015 10:59:14 GMT

Hi,

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

Project	HeadCount	Skill	Level
ABC	3	xyz	2
		cde	3
		ghik	4

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.

Re: Loading Issue

Anonymous — Thu, 19 Feb 2015 11:14:49 GMT

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.

Re: Loading Issue

Thu, 19 Feb 2015 11:22:56 GMT

Thanks for you reply.

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

Kindly help in this.

Thanks,

Sindhu.

Re: Loading Issue

Anonymous — Thu, 19 Feb 2015 11:26:00 GMT

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.

Re: Loading Issue

sushil353 — Thu, 19 Feb 2015 11:27:32 GMT

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

Re: Loading Issue

Anonymous — Thu, 19 Feb 2015 11:41:45 GMT

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

Re: Loading Issue

Thu, 19 Feb 2015 12:09:18 GMT

Hi,

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

Can you please help in this regard.

--Sindhu.

Re: Loading Issue

Anonymous — Thu, 19 Feb 2015 12:13:32 GMT

Hi Sindhu,

Have you tried above code i posted

Regards

Neetha

Re: Loading Issue

Thu, 19 Feb 2015 12:20:20 GMT

Hi Neetha,

Can you help me in understanding the logic used.

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

Regards,

Sindhu

Re: Loading Issue

Thu, 19 Feb 2015 12:26:56 GMT

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

Re: Loading Issue

Anonymous — Thu, 19 Feb 2015 12:40:26 GMT

Hi Sindhu,

first load one excel spreadsheet from script editor - by clicking Table files and procedd.

next Try to implement above code by modifying the load script.

then if any issues update here.

NoOfRows('XlsTables') function Counts tables in xlsTables

FOR i = 0 TO $(vRows)-1 - read each table into loop

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

load statement is executed till the end of rows is reached in xlsTables

if $(vFieldCount) > 10 then - it check for if sheet has more than 10 fields than only it loads that sheet.

hope it helps

Regards

Neetha

Re: Loading Issue

Fri, 20 Feb 2015 05:39:48 GMT

Hi Neetha,

It is generating following error..

Let me know how to proceed.

Thanks,

Sindhu.

Re: Loading Issue

Anonymous — Fri, 20 Feb 2015 07:39:06 GMT

Please post your script,so can help.

Regards

Neetha

Re: Loading Issue

Peter_Cammaert — Fri, 20 Feb 2015 07:43:49 GMT

The error is because vRows is not properly created (the code is missing from neetha P's last post) or because XlsTables does not exist (NoOfRows() returns Null, effictively destroying the variable).

Please post your script as asked.

Re: Loading Issue

Anonymous — Fri, 20 Feb 2015 07:47:59 GMT

Have you created XlsTables as below in script:

Re: Loading Issue

daveamz — Fri, 20 Feb 2015 08:04:48 GMT

Hi,

Load the excel files with explicit column names:

LOAD

Project,

HeadCount,

Skill,

Level

FROM ...\ *.xlsx

Normally, when QlikView will try to load a file were 'HeadCount' is not present will raise an error.

So, you can SET ErrorMode = 0; before LOAD statement -> this will say QlikView to ignore any error and skip to the next file => Finally it will load only the files with all columns present and ignore those without.

David

Re: Loading Issue

Colin-Albert — Fri, 20 Feb 2015 08:08:59 GMT

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

Re: Loading Issue

Fri, 20 Feb 2015 08:51:40 GMT

Thanks for your help

It worked.

--Sindhu.