topic Re: To drop specific fields in a table? in QlikView

To drop specific fields in a table?

Tue, 15 Dec 2015 17:38:11 GMT

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

Re: To drop specific fields in a table?

Colin-Albert — Tue, 15 Dec 2015 17:50:16 GMT

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?

sunny_talwar — Tue, 15 Dec 2015 17:50:39 GMT

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;

Re: To drop specific fields in a table?

Wed, 16 Dec 2015 09:43:17 GMT

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?

Re: To drop specific fields in a table?

Wed, 16 Dec 2015 09:45:15 GMT

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?

Colin-Albert — Wed, 16 Dec 2015 10:10:10 GMT

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?

PrashantSangle — Wed, 16 Dec 2015 10:15:32 GMT

Hi,

You can try

load only those field which you required.

Regards

Re: To drop specific fields in a table?

Siva_Sankar — Wed, 16 Dec 2015 10:18:24 GMT

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?

sunny_talwar — Wed, 16 Dec 2015 10:28:40 GMT

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;

Re: To drop specific fields in a table?

effinty2112 — Wed, 16 Dec 2015 10:45:52 GMT

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

Re: To drop specific fields in a table?

sunny_talwar — Wed, 16 Dec 2015 10:47:46 GMT

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.

Re: To drop specific fields in a table?

Kushal_Chawda — Wed, 16 Dec 2015 10:49:50 GMT

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;

Re: To drop specific fields in a table?

effinty2112 — Wed, 16 Dec 2015 10:51:07 GMT

Oops!

thanks Sunny!

Re: To drop specific fields in a table?

effinty2112 — Wed, 16 Dec 2015 11:00:54 GMT

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

Re: To drop specific fields in a table?

Wed, 16 Dec 2015 11:24:56 GMT

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

Re: To drop specific fields in a table?

Wed, 16 Dec 2015 11:30:32 GMT

Hi Andrew,

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

Re: To drop specific fields in a table?

MayilVahanan — Wed, 16 Dec 2015 11:53:15 GMT

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;

Re: To drop specific fields in a table?

Siva_Sankar — Wed, 16 Dec 2015 14:03:39 GMT

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;

Re: To drop specific fields in a table?

effinty2112 — Wed, 16 Dec 2015 14:12:54 GMT

Hi Prashanth,

Change the line

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

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

Re: To drop specific fields in a table?

Wed, 16 Dec 2015 14:16:50 GMT

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