topic Re: Crosstable and where in QlikView

Crosstable and where

Tue, 15 Mar 2016 13:56:26 GMT

Hi,

I made a crosstable of a csv file, now should I apply a where clause to exclude some lines, why Qlik did not leave it? how can you fix this?

This is the frame of script:

FOR Each file in FileList('..\..\IMPORT\MOVIMENTI\ORE\MENSILE\New\*.csv')

CrossTable(C_CAUSALI, W_ORE_TOTALI, 7)

ORE_T:

LOAD text(mid(FileBaseName(),1,3)) AS C_AZIENDA,

text(SubField(FileBaseName(), ' ', 3)) AS ANNO,

FileName() AS FILENAME,

*

FROM $(file) (txt, codepage is 1252, embedded labels, delimiter is ';', msq, header is 2 lines);

NEXT file;

Thank you in advance.

Re: Crosstable and where

sunny_talwar — Tue, 15 Mar 2016 14:00:16 GMT

May be do the exclusion in the resident load after you have looped through all your CSVs

Re: Crosstable and where

alexandros17 — Tue, 15 Mar 2016 14:04:38 GMT

You can apply where conditions only on another load before your crosstable loading so for example

MyTab

Crosstable (Month, Sales) Load * from ex1.csv;

MyNewTab: noconcatenate

load * residente MyTab Where ....;

drop table MyTab;

Re: Crosstable and where

jonathandienst — Tue, 15 Mar 2016 14:06:42 GMT

What does the where clause look like? Is it based on the field content before or after the cross-table?

Re: Crosstable and where

Tue, 15 Mar 2016 14:22:26 GMT

The clause is based on an existing field, exsisting before crosstable. I did the resident after but nothing has changed.

NoConcatenate

ORE_T2:

LOAD * Resident ORE_T Where not IsNull(MATRICOLA);

DROP Table ORE_T;

Re: Crosstable and where

sunny_talwar — Tue, 15 Mar 2016 16:04:46 GMT

If its based on a field from your csv, then do it before you do the crosstable load.

Re: Crosstable and where

Mon, 21 Mar 2016 16:22:49 GMT

Perfect, it works.

Thank you so much.