Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Nicole-Smith

Remove columns from load by name, not position

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.

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

You can try:

filters(

rotate(left),

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

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

rotate(right)

)

View solution in original post

9 Replies
MK_QSL
MVP
MVP

Can you load your sample file?

Nicole-Smith
Author

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

VendorIDOtherFieldEmp1Emp2Emp3
14921253
14942254
14963245
14984353
15005345

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
Not applicable

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
Partner - Specialist
Partner - Specialist

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

LOAD *

FROM Table;

Drop Field NameOfField;

Nicole-Smith
Author

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
Specialist II
Specialist II

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

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
Not applicable

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
Author

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).