Automatically drop fields with 0 information density in script

[hugmarcel]

Hi

I want to automatically drop all table fields in script, which have 0 Information density (= NULL values only). How can I achieve this?

Thx a lot!

Marcel

[prieper]

Hi,

for individual fields you always need to specifiy, what to do:

LOAD * FROM ... WHERE LEN(TRIM(MyField)) > 0

this would exclude all records containing this field, when it is empty.

For total records, i.e. when there is no information in all fields, this is suppressed automatically by QV.

Else specify your question further

HTH Peter

[hugmarcel]

Hi

thank you, of course I can always check all fields individually by something as

LOAD * FROM ... WHERE LEN(TRIM(MyField)) > 0

However, this solution is very time consuming. I was hoping to find a more elegant table function, such as

FOR i = 0 TO NoOfTables() - 1

theTable = TableName(i);

FOR i = 1 TO NoOfFields('$(theTable)');

  theField = FieldName(i, '$(theTable)');

  IF ((not Density('$(theField)')) THEN

   DROP FIELD [$(theField)];

   TRACE $(theField);

  END IF

NEXT

NEXT

Regards - Marcel

[prieper]

Have never tried,

if so, it would only work via aggregation funtions like CONCAT and then the check, whether the length of the concatenated field is longer than 0 ....

But depending the size of the tables this might be quite timeconsuming.

Regards Peter

[prieper]

additional thought:

why would you load fields without a content? will these field then never have data in it?

Peter

[jagan · Accepted solution]

Hi,

Try this using FieldValueCount().

FOR j = 0 TO NoOfTables() - 1

theTable = TableName(j);

FOR i = 1 TO NoOfFields('$(theTable)');

  theField = FieldName(i, '$(theTable)');

  LET vFieldValueCount = Alt(fieldvaluecount('$(theField)'), 0);

  IF Not vFieldValueCount > 0  THEN

   DROP FIELD [$(theField)];

   TRACE $(theField);

  END IF

NEXT

NEXT

Regards,

Jagan.

[hugmarcel]

thank you, I was searching for this.

[timanshu]

Hi Jagan,

Nice answer provided by you.

However, Can you provide a solution if I want to drop fields that contain either Blank or null?

I found a bit complex solution:

FOR i = 0 TO NoOfTables() - 1

theTable = TableName(i);

FOR i = 1 TO NoOfFields('$(theTable)');

  theField = FieldName(i, '$(theTable)');

  DropField:

  LOAD Sum($(theField)) as Check1, Concat($(theField)) as Check2 Resident $(theTable);

  IF ( Peek('Check1',0,'DropField') =0 and   Peek('Check2',0,'DropField')='')THEN

   DROP FIELD [$(theField)];

   TRACE $(theField);

  END IF

     DROP Table DropField;

NEXT

NEXT

any better way to this solution?

[jagan]

Hi,

Try this

FOR j = 0 TO NoOfTables() - 1

theTable = TableName(j);

FOR i = 1 TO NoOfFields('$(theTable)');

  theField = FieldName(i, '$(theTable)'); 

  DropField:

  LOAD 1 as Check2

  Resident $(theTable)

  WHERE Len(Trim($(theField))) > 0;

  LET vNoOfRows = NoOfRows('DropField');

  DROP TABLE  DropField;

  IF vNoOfRows = 0  THEN

   DROP FIELD [$(theField)];

   TRACE $(theField);

  END IF

NEXT

NEXT

Regards,

Jagan.

[timanshu]

Hi,

However this still requires to write a load statement but still we don't require  to use sum and contact functions.

will probably  increase performance.

Thanks Jagan.