3 Replies Latest reply: Nov 10, 2015 1:29 PM by Massimo Grossi RSS

    Qlikview alters zero-padded values

    Chris Rosen

      Dear all,

       

      I've a problem loading in Qlikview particular codes from a dBase file.

      The script I'm using is very simple:

       

      ODBC CONNECT32 TO [dBase Files];

       

      SQL SELECT *

      FROM .\File.dbf;

       

      Some fields in the source DBF file use a particolar coding system in which, for example, '52', '052', '0052' are three completely different codes. Qlikview seems to fail to load correctly these values, randomly adding or removing inital '0' digit, for example:

      code '0042' becomes '042' and  code '539' becomes '0539'.

       

      I've tried to change the ODBC driver (MS Access dBase Driver v. 15) with an OLEDB driver (Visuale FoxPro) but the problem still remains; using the same ODBC driver in excel the values are loaded correctly so it isn't a bug of the driver and the original dfb file isn't damaged.

       

      How can I force Qlikview to load these values without trying to alter/modify/interpret anything?

       

      Thanks!

        • Re: Qlikview alters zero-padded values
          Massimo Grossi

          try adding text in the preceding load to these fields

           

          load

               text(field) as field,

               text(field2) as field2,

               ..... other fields

                ...

               ;

          SQL SELECT *

          FROM .\File.dbf;

          • Re: Qlikview alters zero-padded values
            Chris Rosen

            Dear maxgro,

             

            thank you very much: using preceding load QV the values are correctly loaded. At this point, the only problem is that the correct values are renamed, in fact the commands:


            load

                 text(field) as field,

            ...

             

            don't work because 'field' is already present in the table; instead I've used:


            load

                 text(field) as field_NEW,

            ...

             

            I think I'll have to load again the table to bring back 'field_NEW' to the original name and drop out the wrong values:

             

            load

                 field_NEW as field,

                 ....

            Resident ....;

             

            Carlo

            • Re: Qlikview alters zero-padded values
              Massimo Grossi

              the preceding load doesn't change the names if you rename the field with the same name of the SQL SELECT ....

               

              without preceding load: you have 2 fields with names field1 and field2

              SQL SELECT

                   field1,

                   field2

              FROM TABLE;

               

               

              with preceding load: you have the same 2 fields with the same names field1 and field2

              load

                   text(field1) as field1,

                   text(field2) as field2;

              SQL SELECT

                   field1,

                   field2

              FROM TABLE;

               

               

              What I mean is that if you have a problem with field1 already present in the table, this should happen with or without preceding load.