2 Replies Latest reply: Mar 23, 2017 9:08 AM by Martin Haseneyer RSS

    Load field value from different source fields

    Martin Haseneyer

      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

        • Re: Load field value from different source fields
          Marcus Sommer

          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