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

    Load field value from different source fields

    Martin Haseneyer



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

      NOCONCATENATE LOAD * RESIDENT [$(vSourceFile)](qvd);
           // more default cleansing and transformations
      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 
           // other handling
      END IF


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

                FieldNumber('GLOBALID', 'temp') > 0,
           ) 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?



      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:



          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



          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