How to check if field (column) exists

[#1]

Hello,

I'm loading in a table that MIGHT not have a particular column. I thought I could check for it by doing an 'if exists(fieldname)' in the load script before referring to it, but when I add that logic, the script fails unceremoniously, going straight to "execution of script failed. load old data?"

I've attached a simple qvw as an example. Note it will reload ok, unless you uncomment the "if" -

I must be using the exists function incorrectly (although I wish it handled the error better). I'm trying either to make this work or find another simple way to NOT execute a portion of the script if a particular field doesn't exist.

Thanks in advance for any advice.

[#2 · Accepted solution]

Hi

Try this code and it should work for you

InlineTable:
LOAD * INLINE [
Field1, Field2
1, 1
2, 3
2, 4
];
Let X = FieldNumber(Field1,'InlineTable');

if FieldNumber(Field1,'InlineTable') >= 0 then
DupeTable:
load
Field1 as NewField1,
Field2 as NewField2
resident InlineTable;
end if

Talha

[#3]

THANK YOU Talha - that's exactly what I needed!

I made only a couple minor changes (I used the variable in the condition, and made condition "greater than" instead of "greater than or equal to" zero):

InlineTable:
LOAD * INLINE [
Field Name1, Field2
1, 1
2, 3
2, 4
];

Let X = FieldNumber('Field Name1','InlineTable');

if X>0 then
DupeTable:
load
[Field Name1] as NewField1,
Field2 as NewField2
resident InlineTable;
end if

Thanks again

[rakeshshah]

Hi Monty/Anyone,

Thanks for the answer on this - is there a way to do this more dynamically inside the load statement. The reason I ask is we have a 60+ QVD tables (weekly snapshots) and I don't want to load each QVD/table twice.

Thanks

Rakesh

[megabyte23]

This is old, but you could do it in a subroutine.

[Sakura]

Could you please provide a small example of using subroutine? 

I have to check if the column exists from the source table then load the column else load some text in the column.