Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted

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.

Tags (2)
1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

Re: Remove columns from load by name, not position

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

Re: Remove columns from load by name, not position

Can you load your sample file?

Re: Re: Remove columns from load by name, not position

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?

gabriel_kirst
Contributor III

Re: Remove columns from load by name, not position

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;

Partner
Partner

Re: Remove columns from load by name, not position

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

LOAD *

FROM Table;

Drop Field NameOfField;

Re: Remove columns from load by name, not position

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
Valued Contributor II

Re: Remove columns from load by name, not position

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

MVP
MVP

Re: Remove columns from load by name, not position

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

gabriel_kirst
Contributor III

Re: Remove columns from load by name, not position

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

Re: Remove columns from load by name, not position

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