How do I exclude columns during a CrossTable load?

[#1]

I want to pull data into QlikView and exclude certain rows and columns. Referencing the example table, how do I modify the where statement to also exclude columns 300-Alloc-Exp and 400-Alloc-Inc? I have tried things that seem logical to me but they haven’t worked as I expected.

Spend:
CrossTable([GL Name], Spend,2)
LOAD
*
FROM [SourceFiles\Monthly\*.xlsm] (ooxml, embedded labels, table is [Sheet1])
where left(Location,3)<>999;

Location	100-Rent	200-OPEX	300-Alloc-Exp	400-Alloc-Inc	500-Labor
001-Austin	10	10	10	10	10
002-Dallas	20	20	20	20	20
999-Totals	30	30	30	30	30

Thanks,

Jody

[swuehl]

Just don't use the star symbol, state all fields:

Spend:
CrossTable([GL Name], Spend,2)
LOAD
Location,

100-Rent,

200-OPEX,

500-Labor
FROM [SourceFiles\Monthly\*.xlsm] (ooxml, embedded labels, table is [Sheet1])
where left(Location,3)<>999;

Or filter records based on resulting field [GL Name] in a subsequent resident load.

[#3]

I can't state all the fields because I get a monthly file where the columns may change over time.I'll investigate using a resident load as you suggest. Thanks for the response.

[swuehl · Accepted solution]

Something like

RESULT:

NOCONCATENATE

LOAD * RESIDENT Spend

WHERE not match([GL Name],'300-Alloc-Exp','400-Alloc-Inc');

DROP TABLE Spend;

[#5]

Thank you. Worked as explained.