check whether a field exists in a QVD

[#1]

    Hi all,

I have a project in 3 layers: exctract-> process -> UI

the extract process is an incremental load from data bases.

now, in order for my model to be compatible with older versions of the system I need to check if a certain field exists

in some tables, and if not I need to create this field in this QVDs and populate it with constant value.

is there some function for checking if a field exists?

if yes, then how can I do the next part of creating this field in populating it?

Thanks,

Boris

[marcus_malinow · Accepted solution]

Hi Boris,

you could use the FieldNumber function.

If it returns zero, then the field doesn't exist.

Marcus

[tresesco]

If a field is not there in your earlier qvd, you can still force concatenate(use concatenate keyword) another table which has additional fields. The additional fields would have null values in the preceding table.

[Anonymous]

Another option is to use ErrorMode=0 in combination with the ScritpError (error variables in help):

ErrorMode=0;          // continue running on errors

LOAD
FieldToCheck
RESIDENT Table;

IF ScritpError=11 THEN       // 11 means "field not found"
// field doesn't exist
... do something
ELSE
// field exists
... do something else
ENDIF

ErrorMode=1;          // restore deafult error mode

Regards,
Michael

[Clever_Anjos]

Tmp:

noconcatenate First 1 LOAD *

FROM yourqvd.qvd(qvd);

if FieldNumber('yourfield','Tmp') > 0 then

   trace 'There is yourfield';

end if;

drop table Tmp;

[#6]

I understood what I need to do in order to check if a field exists.

Now, if it doesn't exist I want to add it manually to this table with a constant number - let's say '1'.

how can I do it? as I don't know the size of the table.

Thanks!

[Clever_Anjos]

"Tricky" way:

Tmp:

noconcatenate First 1 LOAD *

FROM yourqvd.qvd(qvd);

if FieldNumber('yourfield','Tmp') > 0 then

    LET v = 'yourfield';

else

    LET v = '1';

end if;

drop table Tmp;

LOAD

  field1,

  field2,

  $(v) as yourfield

from yourqvd.qvd(qvd);

[flipside]

You can read the XML metadata of the QVD file to check field existance such as this example, where my qvd has two fields FixedDate and FixedStock ...

QvdFieldHeader:

LOAD FieldName
FROM [FixedData.qvd] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

if FieldIndex('FieldName','FixedDate') > 0 then
x = Msgbox('Field exists');
ActualData:

LOAD

     FixedDate,

     FixedStock

     FROM [FixedData.qvd] (qvd);

else

     x = Msgbox('Field missing');
     ActualData:

     LOAD

     '1' as FixedDate,
     FixedStock
     FROM [FixedData.qvd] (qvd);
end if;

flipside

EDIT:

You could simplify the Load script so it only needs coding in one place and use a bit of dynamic code ...

QvdFieldHeader:

LOAD FieldName
FROM [FixedData.qvd] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

if FieldIndex('FieldName','FixedDate') > 0 then
     x = Msgbox('Field exists');

     set dyncode = FixedDate;

else

     x = Msgbox('Field missing');

     set dyncode = 1;

end if;

ActualData:

LOAD
     $(dyncode) as FixedDate,

     FixedStock

FROM
[FixedData.qvd] (qvd);

[struniger]

Hi,

You could use Rob's qlikview-components, function qvdFieldNumber

Here's the link to the script library: http://code.google.com/p/qlikview-components/

The function will return 0 if the field is not in the QVD.

Good luck

Stefan