topic Re: Remove columns from load by name, not position in QlikView

Remove columns from load by name, not position

Nicole-Smith — Mon, 03 Mar 2014 17:05:43 GMT

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.

Re: Remove columns from load by name, not position

MK_QSL — Mon, 03 Mar 2014 17:07:21 GMT

Can you load your sample file?

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

Nicole-Smith — Mon, 03 Mar 2014 18:11:06 GMT

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?

Re: Remove columns from load by name, not position

Anonymous — Mon, 03 Mar 2014 18:24:10 GMT

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;

Re: Remove columns from load by name, not position

jpapador — Mon, 03 Mar 2014 18:28:27 GMT

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

Nicole-Smith — Mon, 03 Mar 2014 18:35:20 GMT

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.

Re: Remove columns from load by name, not position

jerem1234 — Mon, 03 Mar 2014 19:04:44 GMT

You can try:

filters(

rotate(left),

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

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

rotate(right)

)

Re: Remove columns from load by name, not position

swuehl — Mon, 03 Mar 2014 19:17:45 GMT

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.

CrossTable(Emp, Value)

LOAD VendorID, Emp1, Emp2, Emp3

FROM test.csv

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

Re: Remove columns from load by name, not position

Anonymous — Mon, 03 Mar 2014 19:29:31 GMT

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

Nicole-Smith — Mon, 03 Mar 2014 19:46:54 GMT

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