topic Re: Error while using FieldNumber() in QlikView

Error while using FieldNumber()

Tue, 09 Feb 2016 15:10:12 GMT

Hi,

I have a resident table from where I am loading data into another table. While loading, I check for the existence of a special field (revrec_partner_code) in that resident table using FieldNumber().

Sample Code:

if(FieldNumber('revrec_partner_code','RevRec1')=0,'N.A.',[revrec_partner_code]) as [revrec_partner_code]

resident RevRec1

I get the following error:

Error: Field not found - <revrec_partner_code>

I thought FieldNumber would return 'N.A' since revrec_partner_code column does not exist in the resident table. Can you please help?

Thanks!

Re: Error while using FieldNumber()

hic — Tue, 09 Feb 2016 15:14:30 GMT

You have a field reference to a nonexistent field. Then you get a syntax error which means that the call to FieldNumber() never is made.

if(FieldNumber('revrec_partner_code','RevRec1')=0,'N.A.',[revrec_partner_code]) as [revrec_partner_code]

HIC

Re: Error while using FieldNumber()

swuehl — Tue, 09 Feb 2016 15:20:45 GMT

A possible workaround may be to concatenate your RevRec1 table to an empty header table that shows all fields needed in subsequent loads:

RevRec1:

LOAD

'Dummy' as revrec_partner_code

Autogenerate 0;

CONCATENATE (RevRec1)

LOAD * FROM RevRec1Source;

Sample Code:

if( IsNull( revrec_partner_code),'N.A.',[revrec_partner_code]) as [revrec_partner_code]

resident RevRec1

Re: Error while using FieldNumber()

swuehl — Tue, 09 Feb 2016 16:02:53 GMT

Or if you want to go with FieldNumber(), evaluate the FieldNumber outside the load:

T1:

LOAD

Recno() as Field

//, 1 as Dummy // comment in or out for testing

AutoGenerate 5;

Let vCode = If(FieldNumber('Dummy','T1')>0, 'Dummy',chr(39)&'NA'&chr(39) ) & ' AS Dummy';

T2:

NOCONCATENATE

LOAD Field, $(vCode) Resident T1;

DROP TABLE T1;

Re: Error while using FieldNumber()

Thu, 11 Feb 2016 13:26:29 GMT

Thanks swuehl...it worked like a charm when I put FieldNumber out of Load. I searched in help file earlier but couldn't capitalize on that clue.