topic Re: How do I exclude columns during a CrossTable load? in QlikView

How do I exclude columns during a CrossTable load?

Tue, 02 Jun 2015 15:22:14 GMT

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

Re: How do I exclude columns during a CrossTable load?

swuehl — Tue, 02 Jun 2015 16:07:16 GMT

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.

Re: How do I exclude columns during a CrossTable load?

Tue, 02 Jun 2015 17:00:01 GMT

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.

Re: How do I exclude columns during a CrossTable load?

swuehl — Tue, 02 Jun 2015 17:10:00 GMT

Something like

RESULT:

NOCONCATENATE

LOAD * RESIDENT Spend

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

DROP TABLE Spend;

Re: How do I exclude columns during a CrossTable load?

Tue, 02 Jun 2015 17:43:34 GMT

Thank you. Worked as explained.