Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Filter datas directly in the script

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!

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Filter datas directly in the script

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

5 Replies
sasiparupudi1
Honored Contributor III

Re: Filter datas directly in the script

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

Not applicable

Re: Filter datas directly in the script

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
Honored Contributor III

Re: Filter datas directly in the script

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;

Not applicable

Re: Filter datas directly in the script

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
Honored Contributor III

Re: Filter datas directly in the script

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

Community Browser