Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

To drop specific fields in a table?

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

1 Solution

Accepted Solutions

Re: To drop specific fields in a table?

So you are trying to remove all fields except the ones with Invoice?

Try this:

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

26 Replies

Re: To drop specific fields in a table?

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

Re: To drop specific fields in a table?

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;

Not applicable

Re: To drop specific fields in a table?

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?

Not applicable

Re: To drop specific fields in a table?

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?

Re: To drop specific fields in a table?

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?

Re: To drop specific fields in a table?

Hi,

You can try

load only those field which you required.

Regards

Regards,
Prashant Sangle
Siva_Sankar
Honored Contributor

Re: To drop specific fields in a table?

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

Re: To drop specific fields in a table?

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
Honored Contributor

Re: To drop specific fields in a table?

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

Community Browser