Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Condition Field Load

Hi,

I have the following script to load in numerous tab from an excel spreadsheet. I only need to use row 1 and 2 and then transpose them. All tabs have the same format, it simply that some of them contain data in row 2 and others do not.

I am getting an error "Field not found - <@2>" when there is no data in row 2 of some of the excel tabs. What is best way to get around this error.

for each vSheet in 'A', 'B', 'C',...........etc

Initial_Current_Data:

LOAD

@1 as xy,

@2 as value, 

'$(vSheet)' as table,

FileName() as filename

FROM

[$(Location)]

(ooxml, no labels, table is $(vSheet), filters(

Transpose()

));

next;

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Check this script.

Set vFilePath = 'C:\Users\918271\Desktop\test.xlsx';


for each vSheet in 'A', 'B', 'C', 'D', 'E'

RowCount:
LOAD A
FROM
'$(vFilePath)'
(
ooxml, no labels, table is $(vSheet)) Where RecNo()=2;

Let vRowCount = Peek('A',0,'RowCount');
DROP Table RowCount;

If Len(Trim('$(vRowCount)'))>0 THEN

Initial_Current_Data:
LOAD @1 as xy,
@2 as value
'$(vSheet)'
as table,
FileName() as filename
FROM
'$(vFilePath)'
(
ooxml, no labels, table is $(vSheet), filters(
Transpose()
));


ENDIF


next;

View solution in original post

11 Replies
zhadrakas
Specialist II
Specialist II

i tried to reconstruct your problem. For me it will load it without error even if there are no data.

you can try

1) if(len(trim(@2))=0,  null(), @2) as value

but i dont think that this will work

2) can try to set error mode to 0

SET ErrorMode = 0;


//your load statement


SET ErrorMode = 1

i dont know if this ignores the error or simply skips the failed load statement. try it

Anonymous
Not applicable
Author

Thanks for this Tim.

Option 1 does not work as it is still calling out the "field" @2 which does not exist. I had tried this error.

Option 2 does work but I am worried that I will miss errors in my script - its a long one - by taking this route.

Do you happen to know how to count the number of rows per table (i.e. for the loop) and then load only those tables that have a row count of over greater than 1?

tamilarasu
Champion
Champion

Hi Hasham,

I have also tried to replicate the problem but it is working fine. Could you attach a sample file.

Anonymous
Not applicable
Author

Please see attached.

for each vSheet in 'A', 'B', 'C', 'D', 'E'

Initial_Current_Data:

LOAD @1 as xy,

@2 as value,  

     '$(vSheet)' as table,

     FileName() as filename

FROM

(ooxml, no labels, table is $(vSheet), filters(

Transpose()

));

next;

zhadrakas
Specialist II
Specialist II

you can also try to Change your Format

from

(ooxml)

to

(txt)

tamilarasu
Champion
Champion

Check this script.

Set vFilePath = 'C:\Users\918271\Desktop\test.xlsx';


for each vSheet in 'A', 'B', 'C', 'D', 'E'

RowCount:
LOAD A
FROM
'$(vFilePath)'
(
ooxml, no labels, table is $(vSheet)) Where RecNo()=2;

Let vRowCount = Peek('A',0,'RowCount');
DROP Table RowCount;

If Len(Trim('$(vRowCount)'))>0 THEN

Initial_Current_Data:
LOAD @1 as xy,
@2 as value
'$(vSheet)'
as table,
FileName() as filename
FROM
'$(vFilePath)'
(
ooxml, no labels, table is $(vSheet), filters(
Transpose()
));


ENDIF


next;

Anonymous
Not applicable
Author

thanks Tim - no error but the output is now incorrect.

Capture.PNG

Anonymous
Not applicable
Author

this work perfectly. thank you so much.