Remove Empty columns (All Null values) in script dynamically

    Hi All,

    The below script helps to remove the empty columns (which has all null values) dynamically from the DataModel.  To use just copy the below script and paste at the end of your script.

     

    FOR a = 0 TO NoOfTables() - 1
        LET vTable = TableName($(a));
        LET d = 0;
        FOR i = 1 TO NoOfFields('$(vTable)')
            LET j = i - d;
            LET vField = FieldName($(j), '$(vTable)');
            LET vFieldValueCount = Alt(FieldValueCount('$(vField)'), 0);
            IF (vFieldValueCount <= 0 ) THEN
             DROP FIELD [$(vField)] FROM $(vTable);
             TRACE DROP FIELD [$(vField)] FROM $(vTable);
             LET d = d + 1; // dropping fields impacts the internal field no.
            END IF
           NEXT i
        NEXT a
    

     

    If you want the empty values (whitespaces) then use below script

     

    FOR a = 0 TO NoOfTables() - 1
      LET vTable = TableName($(a));
      LET d = 0;
      FOR i = 1 TO NoOfFields('$(vTable)')
      LET j = i - d;
      LET vField = FieldName($(j), '$(vTable)');
      DropField:
      LOAD 1 as Check2
      Resident $(vTable)
      WHERE Len(Trim($(vField))) > 0;
    
      LET vNoOfRows = NoOfRows('DropField');
      DROP TABLE  DropField;
      IF vNoOfRows = 0  THEN
      DROP FIELD [$(vField)] FROM $(vTable);
      TRACE DROP FIELD [$(vField)] FROM $(vTable);
      LET d = d + 1; // dropping fields impacts the internal field no.
      END IF       
      NEXT i
    NEXT a
    

     

    You can test the above script using below Inline table.

     

    Data:

    LOAD

    *,

    ' ' AS Field3,

    Null() AS Field4

    INLINE [

    Field1, Field2

    1,2

    3,4

    5,6];

     

    When you use the first scrip then Field4 will be dropped, and when you use second script then both Field3 and Field4 are dropped.

     

    Hope this helps.

     

    Regards,

    jagan.