topic Re: Condition Field Load in QlikView

Condition Field Load

Anonymous — Thu, 18 Jan 2018 13:16:58 GMT

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;

Re: Condition Field Load

zhadrakas — Thu, 18 Jan 2018 16:18:52 GMT

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

Re: Condition Field Load

Anonymous — Thu, 18 Jan 2018 20:17:11 GMT

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?

Re: Condition Field Load

zhadrakas — Thu, 18 Jan 2018 21:26:20 GMT

check the noOfRows Function

http://help.qlik.com/de-DE/qlikview/12.1/Subsystems/Client/Content/Scripting/TableFunctions/NoOfRows.htm

Re: Condition Field Load

tamilarasu — Fri, 19 Jan 2018 02:40:14 GMT

Hi Hasham,

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

Re: Condition Field Load

Anonymous — Fri, 19 Jan 2018 09:22:21 GMT

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;

Re: Condition Field Load

zhadrakas — Fri, 19 Jan 2018 09:41:00 GMT

you can also try to Change your Format

from

(ooxml)

(txt)

Re: Condition Field Load

tamilarasu — Fri, 19 Jan 2018 09:44:38 GMT

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;

Re: Condition Field Load

Anonymous — Fri, 19 Jan 2018 09:44:53 GMT

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

Re: Condition Field Load

Anonymous — Fri, 19 Jan 2018 09:47:31 GMT

this work perfectly. thank you so much.

Re: Condition Field Load

zhadrakas — Fri, 19 Jan 2018 09:48:48 GMT

i think the utf8 Parameter is missing here.

like this. probably you Need to Change the delimeter parameter

(txt, utf8, no labels, delimiter is ';', msq)

Re: Condition Field Load

tamilarasu — Fri, 19 Jan 2018 09:49:52 GMT

You are welcome. Have a nice day!.