Filter datas directly in the script

[#1]

Hi!

Despite of all my research and tests to find my way out, I need your help :

I am importing datas from a SQL table (that I can't modify o filter), and I want to filter datas I am importing because not everything is usefull to me.

For exemple, in my field "SerieAlbaran", I don't want to import datas named "0". I tried everything I found in the forum and nothing is working.

So here is my script and how can I filter my datas directly from my script?

[ZVentasSiberFdo]:

LIB CONNECT TO 'Microsoft_SQL_Server_HPSERVER';

[ZVentasSiberFdo]:

LOAD

  Left(SerieAlbaran,2)&'-'& mid(SerieAlbaran,3,2)&'-'&right(SerieAlbaran,5) as Hijo,

  EjercicioAlbaran,

  date(FechaAlbaran, 'MMM/yyyy') as [Fecha Albarán],

  Year(FechaAlbaran);

[ZVentasSiberFdo]:

SELECT SerieAlbaran,

  EjercicioAlbaran,

  FechaAlbaran,

FROM SIBERZONE1.dbo.ZVentasSiberFdo;

Thanks for your help!

[sasiparupudi1]

LIB CONNECT TO 'Microsoft_SQL_Server_HPSERVER';

[ZVentasSiberFdo]:

LOAD

  Left(SerieAlbaran,2)&'-'& mid(SerieAlbaran,3,2)&'-'&right(SerieAlbaran,5) as Hijo,

  EjercicioAlbaran,

  date(FechaAlbaran, 'MMM/yyyy') as [Fecha Albarán],

  Year(FechaAlbaran);

SELECT SerieAlbaran,

  EjercicioAlbaran,

  FechaAlbaran,

FROM SIBERZONE1.dbo.ZVentasSiberFdo where SerieAlbaran<>0;

hth

Sasi

[#3]

I already tried the "where SerieAlbaran <>0" but it is giving me that error :

ZVentasSiberFdo << QueryResult Error de script: QVX_UNEXPECTED_END_OF_DATA: ERROR [22018] [Qlik][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Error de conversión al convertir el valor varchar 'SI1252C' al tipo de datos int. ERROR [HY008] [Qlik][ODBC SQL Server Wire Protocol driver]Operation cancelled.

Se ha producido el siguiente error:

Custom read failed

El error se ha producido aquí:

[ZVentasSiberFdo]:

SELECT SerieAlbaran, EjercicioAlbaran, FechaAlbaran FROM SIBERZONE1.dbo.ZVentasSiberFdo where SerieAlbaran<>0

[sasiparupudi1]

Oh it looks like it is a string field in your db.

try

LIB CONNECT TO 'Microsoft_SQL_Server_HPSERVER';

[ZVentasSiberFdo]:

LOAD

  Left(SerieAlbaran,2)&'-'& mid(SerieAlbaran,3,2)&'-'&right(SerieAlbaran,5) as Hijo,

  EjercicioAlbaran,

  date(FechaAlbaran, 'MMM/yyyy') as [Fecha Albarán],

  Year(FechaAlbaran);

SELECT SerieAlbaran,

  EjercicioAlbaran,

  FechaAlbaran,

FROM SIBERZONE1.dbo.ZVentasSiberFdo where SerieAlbaran<>'0';

or

LIB CONNECT TO 'Microsoft_SQL_Server_HPSERVER';

[ZVentasSiberFdo]:

LOAD

  Left(SerieAlbaran,2)&'-'& mid(SerieAlbaran,3,2)&'-'&right(SerieAlbaran,5) as Hijo,

  EjercicioAlbaran,

  date(FechaAlbaran, 'MMM/yyyy') as [Fecha Albarán],

  Year(FechaAlbaran);

SELECT SerieAlbaran,

  EjercicioAlbaran,

  FechaAlbaran,

FROM SIBERZONE1.dbo.ZVentasSiberFdo where len(trim(SerieAlbaran))>0;

[#5]

Your first option is working! Thanks!

Last question : how do I put various "Where" in order to have various filters? What is the syntax? (I am a little bit confused about the way to write correctly the expressions)

For example : filtering "0" and "1" from "SerieAlbaran" + filtering years before 2013 from "EjercicioAlbaran"?

[sasiparupudi1 · Accepted solution]

it also depends on your data formats

for example

SELECT SerieAlbaran,

  EjercicioAlbaran,

  FechaAlbaran,

FROM SIBERZONE1.dbo.ZVentasSiberFdo where SerieAlbaran not in ('0','1')

and year(EjercicioAlbaran)>=2013

Assuming that EjercicioAlbaran is a date field

hth

Sasi