How to Avoid Field not found error ? we have to replace missed field with Null

[SatyaPaleti]

Hi Everyone,

How to Avoid Field not found error?

if the field is not available we have to consider that field as Null() but qlik should not through an error.

In Detail:

I am having Test_123 File. 

So when I am loading Data from Test_123.

Load

Id, Contact, Forms, Status ;

Select id, Contact, Forms From xyz."Test 123" 

so in Table Test_123 there is no Status Field. So Qliksense will through an error. We have to avoid this error.

I have Implemented a logic for this but not working. Can some one please help me what step need to implement to avoid the error.

let vTableName = 'Test_123';
Test_123:
LOAD
* ;

SELECT *
FROM "dm_air_test".$(vTableName);

FOR i = 1 to NoOfFields('Test_123');

Fields:
LOAD
FieldName($(i),'Test_123') as FieldName1
AutoGenerate 1

NEXT i

drop table Test_123;

FOR EACH vFields IN FieldValueList('FieldName1');

IF vFields like 'Status' THEN

LET vField = '[Status]';

ELSE

LET vField = 'Null() as [Status]';
EndIf
next vFields;

Test:

 Load *,

$(vField);

Select * from XYZ.Test_123

"FOR EACH vFields IN FieldValueList('FieldName1')"  This statement always taking to else condition only. 

Can some one please help me to fix this

Thanks,

Satya

[AronC]

In your code there is a typo at autogenerate. I chaged to "AutoGenerate (1);"

Also when you read your table "Test" at the end you are going to have a problem if field "Status" exist. Because you are first reading * which includes the field Status and then adding "Status" again.

When running you code I did get true though in the first if-statement if Status-field exists.

I do have a code though that I use in my own extract that might work for you.

Tmp:
LOAD *;
SQL SELECT top 10
*
FROM EDW."$(Schema)".$(TableName);


set vCastField =;
if FieldNumber('FieldToCast','Tmp') > 0 then
       set vCastField = ,CAST(FieldToCast AS VARCHAR(20)) AS FieldToCast_New;
end if

drop table Tmp;

Tmp2:
LOAD *;
SQL SELECT
*
$(vCastField )
FROM EDW."$(Schema)".$(TableName);

[marcus_sommer · Accepted solution]

You don't need the for-loops else a direct check on the field is enough, like:

if len(fieldvaluecount('Status')) then
set vField = [Status];
else
set vField = null() as [Status];
end if

Further I would suggest not to query the entire table else using a First/Top/Limit or similar statement (depends on your data-base which statement with which syntax is supported) to pull just one record.

Another way might be to query the system-tables of your data-base to find out which fields are there.