Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I exclude columns during a CrossTable load?

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Something like

RESULT:

NOCONCATENATE

LOAD * RESIDENT Spend

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

DROP TABLE Spend;

View solution in original post

4 Replies
swuehl
MVP
MVP

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.

Not applicable
Author

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

Something like

RESULT:

NOCONCATENATE

LOAD * RESIDENT Spend

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

DROP TABLE Spend;

View solution in original post

Not applicable
Author

Thank you. Worked as explained.