Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thank you, works perfectly.