topic Re: where clause not working in QlikView

where clause not working

vvvvvvizard — Tue, 23 Jun 2015 07:35:54 GMT

I have a list of excel files 201401.xlsx,201402.xlsx,201403.xlsx ..... until 201505.xlsx

the first loads works perfect where i wna load all data before 201404 including 201404

Load

FROM

(ooxml, embedded labels, table is Export)

Where SubField(Filename(),'.',1)<=201504;

the second load gives alot of table not found error

FROM

(ooxml, embedded labels, table is Export)

Where SubField(Filename(),'.',1)>201504;

This works fine

so if i changed it to load 201505 file only i get no table found errors, and the field names in the previous load is a copy and past to the ones used here , so this tells me its the where clause and not the fields missing

FROM

(ooxml, embedded labels, table is Export);

Re: where clause not working

swuehl — Tue, 23 Jun 2015 07:45:02 GMT

Have you double checked that the Excel file actually contains a sheet called Export and can be loaded using the script Editor wizard?

Re: where clause not working

MayilVahanan — Tue, 23 Jun 2015 07:46:30 GMT

I think

Export sheet is not available in 201505.xlsx.

Please confirm that

Re: where clause not working

PrashantSangle — Tue, 23 Jun 2015 07:47:17 GMT

Hi,

try below

date#(SubField(Filename(),'.',1),'YYYYMM')>Date#(201504,'YYYYMM');

Regards

Re: where clause not working

Gabriel — Tue, 23 Jun 2015 07:51:16 GMT

Hi,

It sound to me as the first table has same name as the second table and if this is the case the second table with concatenate with the first table. usually this is the cause of table not found error. What you might want to do is enter NOCONCATENATE between the 2 tables

Re: where clause not working

swuehl — Tue, 23 Jun 2015 07:54:00 GMT

A second comment, if you want to exclude the files from being loaded, I would use a

For each File in Filelist('C:\Users\E936682\Desktop\Report_Builder_Data\ADDM\*.xlsx')

Next File

to iterate over all files and check the filename in a IF...THEN...LOAD.. ENDIF statement, instead of doing the filename check on a per record base.

Re: where clause not working

vvvvvvizard — Tue, 23 Jun 2015 07:54:29 GMT

from the wizard loads fine, i edited the automatically generated script and added the where clause which gives the table not found error

Re: where clause not working

vvvvvvizard — Tue, 23 Jun 2015 07:55:43 GMT

the second table has a few more fields than the first table , i will outer join the two tables once i get the second table to load

Re: where clause not working

vvvvvvizard — Tue, 23 Jun 2015 09:11:09 GMT

I dont know how to , some guidance please

For Each file in FileList('C:\Users\E936682\Desktop\Report_Builder_Data\ADDM\*');

if(Right(SubField($(file),6),'.',1)<=201504,

LOAD Name,

Annotationapm,

Date(Date#(SubField(Filename(),'.',1),'YYYYMM'),'MMM-YY') as the_date

FROM

[$(file)]

(ooxml, embedded labels, table is Export);)

NEXT file;

Re: where clause not working

swuehl — Tue, 23 Jun 2015 09:21:25 GMT

Try like

For Each file in FileList('C:\Users\E936682\Desktop\Report_Builder_Data\ADDM\*')

IF SubField( file,'.',1) <= 201504 THEN

LOAD Name,

Annotationapm,

Date(Date#(SubField(Filename(),'.',1),'YYYYMM'),'MMM-YY') as the_date

FROM

[$(file)]

(ooxml, embedded labels, table is Export);

ENDIF

NEXT file

I would also suggest running the script in the debugger first to check the values of variables.

Re: where clause not working

vvvvvvizard — Tue, 23 Jun 2015 09:47:11 GMT

Thanks , didnt know about the debugger , tested it works