Remove columns from load by name, not position

[Nicole-Smith]

I am able to remove columns from a cross table load by doing this:

CrossTable(Field, Value)

LOAD *

FROM test.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(

Remove(Col, Pos(Top, 2))

));

However, this removes the column by its position in the file.

Is there a way that I can specify which column to remove by the column name?  The columns that I'm importing aren't always in the same order.

[MK_QSL]

Can you load your sample file?

[Nicole-Smith]

I want to cross table data that looks something like this (file also attached):

VendorID	OtherField	Emp1	Emp2	Emp3
1492	1	2	5	3
1494	2	2	5	4
1496	3	2	4	5
1498	4	3	5	3
1500	5	3	4	5

The following load script results in what I want (I want to remove OtherField and cross table the rest):

CrossTable(Emp, Value)

LOAD *

FROM test.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(

Remove(Col, Pos(Top, 2))

));

However, this only works when OtherField is the second column in the file.  It isn't always the second column in the file, so I need to be able to remove the column by the name, not the position.  I also need to use the asterisk (*) to load in all of the fields, because there may at some point be an Emp4 column, even though there isn't one right now.

So the question is, is there a way in the remove statement to specify the name of the column to be removed instead of specifying the position of the column in the table?

[Anonymous]

What about load your entire file, and after that drop the column that you don't want?

Something like it:

LOAD *

FROM

test.csv.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Drop field OtherField;

[jpapador]

You can use the Drop Field (or Fields if multiple) command

LOAD *

FROM Table;

Drop Field NameOfField;

[Nicole-Smith]

Gabriel Rocha and jpapador--I know I can drop fields, but then I would have to do a resident load for the cross table.  I want to do the cross table during the initial load from the file.  Once the table has gone through the cross table transformation, I can no longer use drop statements.

[jerem1234 · Accepted solution]

You can try:

filters(

rotate(left),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Test'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Test2'))),

rotate(right)

)

[swuehl]

What about explicitely listing the fields you want in the load (instead of LOAD * ...)?

This will also prevent from getting things messed up when your VendorID is accidently not in the first column.

Like

CrossTable(Emp, Value)

LOAD VendorID, Emp1, Emp2, Emp3

FROM test.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[Anonymous]

swuehl If i've understood, new columns can be added, for exemple, how Nicole Smith said,  can be added at some point an Emp4 column . So, any time that it happens, the script will need to be changed.

I've tested @jerem1234 's solution, and it worked here, even changing the position of the column "OtherField".

[Nicole-Smith]

swuehl, Gabriel Rocha is correct in that new columns can be added (such as Emp4), and that needs to be included in the cross table as well, so I am not able to name the fields explicitly.  VendorID will always be in the first column, so that one shouldn't be a problem.  It's the column that I need to remove that can move around (it can be anywhere from the second column to the last).