Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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
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;
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"?
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