Crosstable and where

[#1]

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.

[sunny_talwar]

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

[alexandros17]

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;

[jonathandienst]

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

[#5]

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;

[sunny_talwar · Accepted solution]

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

[#7]

Perfect, it works.

Thank you so much.