topic Load partial data from Excel in QlikView

Load partial data from Excel

Fri, 04 May 2012 19:34:49 GMT

Have numerous excel 2010 workbooks stored in a folder "Days", each with 1 worksheet . Worksheets may have the same or different sheet names across workbooks. I want to load data beginning with row 42. I have differing numbers of rows of data (beginning with row 42) across workbooks.

Here is the load statement:

LOAD filename() as JobNoSheet,

rowno() as EventNo,

From,

To,

Hrs.* 24 as DecimalHours,

[Start Depth] as StartDepth,

[End Depth] as EndDepth,

CL,

Code,

[Time Breakdown] as TimeBreakdown

FROM

(ooxml, embedded labels, header is 37 lines);

This works fine, but I only want to load the data from rows where the value in "Hrs." is not blank.

How do I qualify the load statement to limit the data loaded?

Thanks

Load partial data from Excel

Nicole-Smith — Fri, 04 May 2012 19:46:20 GMT

LOAD filename() as JobNoSheet,

rowno() as EventNo,

From,

To,

if(Hrs. <> NULL() AND Hrs. <> '', Hrs.* 24) as DecimalHours,

[Start Depth] as StartDepth,

[End Depth] as EndDepth,

CL,

Code,

[Time Breakdown] as TimeBreakdown

FROM

(ooxml, embedded labels, header is 37 lines);

Re: Load partial data from Excel

Fri, 04 May 2012 19:54:36 GMT

Thanks

Adding

Where

not isnull(Hrs.);

At the end of the load statement also seems to work.

Is this equivalent to the code you suggested?

Bill

Re: Load partial data from Excel

Nicole-Smith — Fri, 04 May 2012 19:56:08 GMT

Yup, that's the same thing, but mine also checks for empty strings.