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 · Accepted solution]

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

[Peter_Cammaert]

Hi Sunny,

a possible improvement for your two examples could be this shortened version:

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 vDropConcat = Peek('ConcatDropList'); 

DROP Field $(vDropConcat) from Table; 

As an additional safety measure, you can put the last LET/DROP combo in an IF that tests CountDropList for a value > 0

Best,

Peter

[flipside]

Similar to other answers, but this is placed inside a subroutine so you can call it again later in your load script ...

Sub DropFields(tbl,wildcard,switch)
let d = Nothing;

For f = 0 to NoOfFields(tbl)
Switch switch
Case 'Drop'
if wildmatch(FieldName(f,tbl),wildcard) then
let d = d & FieldName(f,tbl) & ',';
endif;
Case 'Save'
if NOT(wildmatch(FieldName(f,tbl),wildcard)) then
let d = d & FieldName(f,tbl) & ',';
endif;
end switch;

next f;

let d = left(d,len(d)-1); //handle final comma

Drop Fields $(d) from $(tbl);
End Sub;

... then call using ...

//Sub routine call - specify table, wildcard and Drop/Save switch (drop means drop fields WITH wildcard match,

//save means drop fields WITHOUT wildcard match)

Call DropFields('Data','Temp*','Save'); 

flipside

[sunny_talwar]

So essentially there is no need to use For Loop? Even better . Thanks for sharing Peter Cammaert

[sunny_talwar]

Prashanth you have chosen a correct answer, but I think Peter brings a good point that this can be done without looping. Not looping makes it very simple:

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
Resident DropFieldsTemp
Where not WildMatch(Fields, 'Invoice *');

DROP Table DropFieldsTemp;

LET vDropConcat ='[' & Peek('ConcatDropList') & ']';

DROP Fields $(vDropConcat) from Table;
DROP Table DropFields;

[#7]

Thanks  @Sunny and @Peter, It's working.

[#8]

Hi all,

how can we manipulate(refer to) with fields generated in the dynamic table