Load field value from different source fields

[#1]

Hello,

I want to load content from QVD files that have almost the same content. Almost... so this code is executed within a FOR loop:

[temp]:
NOCONCATENATE LOAD * RESIDENT [$(vSourceFile)](qvd);

[temp2]:
NOCONCATENATE LOAD
     *,
     // more default cleansing and transformations
RESIDENT
[temp];
DROP TABLE [temp];

There is one downside: the source files contain a field that is sometimes GLOBALID and sometimes USER.ID but consists of the same content. So I want to check "if the source has a GLOBALID field, then use this; otherwise, use USER.ID if available, or drop NULL if none of both is available".

It would work with

IF (FieldNumber('GLOBALID', 'temp') > 0) THEN
     // handling 
ELSE
     // other handling
END IF

But I would have to keep the code aligned for all cases. It would be better to have something like:

NOCONCATENATE LOAD
     If(
          FieldNumber('GLOBALID', 'temp') > 0,
          [GLOBALID],
          [USER.ID]
     ) AS USER_ID
RESIDENT [temp];

This does not work: it seems to evaluate the "yes" case (use GLOBALID) also when the test fails, and therefore drops a "Field not found" error message.

Do you have experiences with this?

Best,

Martin Haseneyer

[marcus_sommer · Accepted solution]

You couldn't check the exists of a field within a load-statement - it must be done before on the outside, for example with the following approach:

CheckFields:

noconcatenate first 1 load * from Source;

let vCheckFields = if(fieldname('CheckFields', 1) = 'USER.ID', true(), false());

maybe also within a loop if the position of the field is variable:

for i = 1 to nooffields('CheckFields')

     if fieldname('CheckFields', 1) = 'USER.ID' then

          let vCheckFields = true();

          exit for;

     end if

next

and then using this within another if-loop to control which load-statement should be used.

Another way might be to use a rename-statement of this field after each single load whereby this approach will require a certain order of all your load-statements.

- Marcus

[#3]

Thank you, works perfectly.