topic Re: Automatically drop fields with 0 information density in script in QlikView

Automatically drop fields with 0 information density in script

hugmarcel — Tue, 07 Apr 2015 08:15:13 GMT

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

Re: Automatically drop fields with 0 information density in script

prieper — Tue, 07 Apr 2015 08:25:22 GMT

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

Re: Automatically drop fields with 0 information density in script

hugmarcel — Tue, 07 Apr 2015 08:32:08 GMT

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

Regards - Marcel

Re: Automatically drop fields with 0 information density in script

prieper — Tue, 07 Apr 2015 08:40:21 GMT

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

Re: Automatically drop fields with 0 information density in script

prieper — Tue, 07 Apr 2015 08:43:50 GMT

additional thought:

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

Peter

Re: Automatically drop fields with 0 information density in script

jagan — Tue, 07 Apr 2015 09:20:13 GMT

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

Regards,

Jagan.

Re: Automatically drop fields with 0 information density in script

hugmarcel — Tue, 07 Apr 2015 09:34:44 GMT

thank you, I was searching for this.

Re: Automatically drop fields with 0 information density in script

timanshu — Tue, 07 Apr 2015 09:54:16 GMT

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

any better way to this solution?

Re: Automatically drop fields with 0 information density in script

jagan — Tue, 07 Apr 2015 10:27:11 GMT

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

Regards,

Jagan.

Re: Automatically drop fields with 0 information density in script

timanshu — Tue, 07 Apr 2015 10:34:24 GMT

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.

Re: Automatically drop fields with 0 information density in script

hugmarcel — Tue, 07 Apr 2015 12:39:34 GMT

Note that dropping a field in a table Impacts the internal field no index. Therefore you should use:

//Drop field with NULL values only, ignore fields in theExceptionFieldList

SUB DropQVFieldsWithNULLValuesOnly ( theExceptionFieldList )

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 AND INDEX('$(vField)', '$(theExceptionFieldList)') <= 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

END SUB

And call the SUB with e.g. CALL DropQVFieldsWithNULLValuesOnly ( 'Booking - not to be deleted' );

Marcel

Re: Automatically drop fields with 0 information density in script

jagan — Wed, 08 Apr 2015 02:27:50 GMT

Good catch Marcel. I created a document in community with this content, because it may help others who are looking for similar one.

Link: Remove Empty columns (All Null values) in script dynamically

Regards,

Jagan.