5 Replies Latest reply: Sep 23, 2015 9:33 AM by Sasidhar Parupudi RSS

    Filter datas directly in the script

    Alexia Martin

      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!

        • Re: Filter datas directly in the script
          Sasidhar Parupudi

          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

            • Re: Filter datas directly in the script
              Alexia Martin

              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

                • Re: Filter datas directly in the script
                  Sasidhar Parupudi

                  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;