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??

[Colin-Albert]

You can use  drop field fieldname;    to drop the field completely

or       drop field fieldname from tablename ;     to drop the field from just the named table.

You can list multiple fields in a list  drop field fieldA, fieldB, fieldC ;

but be aware if any of the fields in the list is missing, then none of the fields are dropped. In this case I prefer to have 3 separate drop statements, that way if a field in missing, the others still will be dropped.

It does not matter if you type   drop field    or   drop fields  they both work the same

[sunny_talwar]

I don't think * will work. You can drop them individually like this:

DROP Fields [invoice month], [invoice date], [invoice line], [invoice quantity] From TableName;

[#4]

Hi Sunny,

I cannot write all the field names. I want do like wildmatch(Invoice*). I have so many fields that needs to be dropped.

Is there any way?

[#5]

Hi Albert,

I cannot write all the fields because there are so many that needs to be dropped. Is there any other way to drop?

[Colin-Albert]

The drop fields statement does not support wildcards, so you will need to list the individual fields

Can you exclude the fields from the initial load statements so they do not get loaded into your data model in the first place?

[PrashantSangle]

Hi,

You can try

load only those field which you required.

Regards

[Siva_Sankar]

Prashanth,

To drop fields using wild card you can refer the below

qliktips.com/2009/10/removing-fields-with-wildcard.html

qlikviewaddict.com/2014/06/dropping-tables-using-wildcard.html

Explained with examples.

Regards,

sivasankar

[sunny_talwar]

Check this code:

Table:
LOAD * Inline [
name, invoice month, invoice date, invoice line, category, sub category, invoice quantity
a,b,c,d,e,f,g
];

DropFieldsTemp:
CrossTable(Fields, Value)
LOAD 1 as Temp,
  *
Resident Table;

DropFields:
LOAD Concat(Fields, ']|[') as ConcatDropList,
  Count(Fields) as CountDropList
Resident DropFieldsTemp
Where WildMatch(Fields, 'Invoice *');

DROP Table DropFieldsTemp;

LET vDropCount = Peek('CountDropList');
LET vDropConcat = Chr(39) & '[' & Peek('ConcatDropList') & ']' & Chr(39);

For i = 1 to $(vDropCount)

  LET vField = SubField($(vDropConcat), '|', $(i)); 
  DROP Field $(vField) from Table;

NEXT

DROP Table DropFields;

[effinty2112]

Hi Prashanth,

try this:

FOR i = NoOfTables()-1 to 0 step -1

Let vTblName = TableName($(i));

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

Let vDropTable = '[$(vTblName)]';

Drop Table $(vDropTable);

END if;

NEXT i