To drop specific fields in a table?

[#1]

Hi all,

I am having a table with field names as below:

[name] [invoice month] [invoice date] [invoice line] [category] [sub category] [invoice quantity]

How to drop the fields that are starting with 'invoice*' ???

Actually i have more fields that i should drop them individually.

Any particular format to drop the fields??

[sunny_talwar]

Andrew -

I think he wants to drop fields within a table and not the tables. I think what you are proposing is to dynamically drop tables. This is great solution, but might now work for Prashanth.

[Kushal_Chawda]

try like below

Data:

LOAD * Inline [

InvoiceNo, InvoiceDate,InvoiceLine, Data,Name,Location

1,2,3,4 ];

for i=1 to NoOfFields('Data')

let vName = FieldName($(i),'Data');

FieldNames:

LOAD '$(vName)' as FieldName AutoGenerate(1);

next

DeleteField:

NoConcatenate

LOAD FieldName as FieldsTobeDeleted

Resident FieldNames

where wildmatch(Lower(FieldName),'invoice*');

for i=1 to FieldValueCount('FieldsTobeDeleted')

LET vDeleteField = FieldValue('FieldsTobeDeleted',$(i));

DROP Field '$(vDeleteField)' From Data;

next

DROP Tables FieldNames,DeleteField;

[effinty2112]

Oops!

thanks Sunny!

[effinty2112]

Hi Prashanth,

this?

FOR i = 0 to NoOfTables()-1

Let vTblName = TableName($(i));

  For k = NoOfFields('$(vTblName)') to 1 step -1

  Let vFieldName = FieldName($(k), '$(vTblName)');

  if Upper(left('$(vFieldName)', 7)) = 'INVOICE' then

  Let vDropField = '[$(vFieldName)]';

  Drop Field $(vFieldName);

     End if;

  NEXT k

NEXT i

[#6]

Hi Sunny,

It's working for fields with invoice.

Now I want to drop the fields other than Invoice. I have tried with <> Invoice, but it throwing an error  as

Did not find the field " " from the DROP FIELD statement

[#7]

Hi Andrew,

This is working and now how can I drop the fields other than 'Invoice' ??

[MayilVahanan]

One more

Table: 

LOAD * Inline [ 

name, invoice month, invoice date, invoice line, category, sub category, invoice quantity 

a,b,c,d,e,f,g 

];

Let vFields = ;

For i = 0 to NoOfFields('Table')

If(not WildMatch(FieldName($(i), 'Table'), '*invoice*')) Then

   vFields = '['&FieldName($(i), 'Table') & '],' &'$(vFields)';

   ENDIF;

NEXT;

Let vFieldName = Mid(vFields, 1, Len(vFields)-1);

FinalTable:

LOAD $(vFieldName) Resident Table;

DROP Table Table;

[Siva_Sankar]

To drop specific field, drop field fieldname;

from Qlik help

drop field[ s ] fieldname [ , fieldname2 ...] [from tablename1 [ , tablename2 ...]]

Both drop field and drop fields are allowed forms with no difference in effect.
If no table is specified, the field will be dropped from all tables where it occurs.

Examples:

drop field A;

drop fields A,B;

drop field A from X;

drop fields A,B from X,Y;

[effinty2112]

Hi Prashanth,

Change the line

if Upper(left('$(vFieldName)', 7)) = 'INVOICE' then

to

if Upper(left('$(vFieldName)', 7)) <> 'INVOICE' then

[#11]

Andrew,

Not in multiple tables,

I tried but its not dropping and throwing an error as

Did not find the field " " from the DROP FIELD statement