4 Replies Latest reply: Jan 13, 2016 4:03 PM by Arthur Lankester RSS

    Conditional load on Fieldname

    Arthur Lankester

      Hi,

       

      I want to load certain fields based on their fieldname.

      I tried to do this with an if statement but I don't get it toe work.

       

      It should be something like this:

       

      LOAD

      if(fieldname contains '*K01*') as fieldname

       

      Does anyone has an idea how to apply conditional load based on fieldnames?

        • Re: Conditional load on Fieldname
          Stefan Wühl

          And you don't know the field names upfront?

           

          Maybe there are better solutions to what you are trying to achieve at the end, for creating a filtered list of field names to load, you can try something like

           

           

          // Only a dummy table to have something to play with
          TABLE:
          LOAD * INLINE [
              Dimension, Metric 1, Metric 2
              1, 1, 10
              2, 2, 9
              3, 3, 8
              4, 4, 7
              5, 5, 6
              6, 6, 5
              7, 7, 4
              8, 8, 3
              9, 9, 2
              10, 10, 1
          ];
          
          // Create a field with all field names
          FIELDS:
          CROSSTABLE (FieldName, Value) LOAD
          1 as Dummy, *
          Resident TABLE
          where RecNo() =1;
          
          
          DROP FIELDS Dummy, Value;
          
          // Create a filtered list of the field names, embedded in [..] to handle spaces etc.
          CONCAT:
          LOAD '[' & CONCAT(If (FieldName Like 'Metric*', FieldName),'], [') & ']' as FilteredFields
          RESIDENT FIELDS;
          
          // Create a variable from that filtered table value
          Let vFilteredFields = Peek('FilteredFields',0,'CONCAT');
          
          // Finally load your filtered table from source, instead of a resident TABLE LOAD, you can load your data directly FROM your source here and in the FIELDS table
          LOAD $(vFilteredFields) RESIDENT TABLE;
          
          //Clean up
          DROP TABLE TABLE, FIELDS, CONCAT;