9 Replies Latest reply: Dec 20, 2012 3:40 AM by sean kaiser RSS

    Material codes are wrong once in QlikView

    sean kaiser

      Hi experts,

       

      I am quite new to QlikView and am struggling to work out why I get the following problem... any advice would be very much appreciated.

       

      I am extracting data from SAP BW, specifically the standard Material table /BIC/PMATERIAL.  I have the following 2 records in that table:-

       

      Material CodeCreated OnEAN/UPC Code
      6069305.11.20047613413
      60693E05.11.20047614780

       

      I am extracting them into a QVD and then loading that QVD into a new application which has nothing else in it.  There are no join, transformations etc.  Just one to one straight out of BW into QlikView.

       

      But when I create a straight table in QlikView I see the following:-

       

      Material CodeCreated OnEAN/UPC Code
      6069305.11.20047613413
      6069305.11.20047614780

       

      The E at the end of the 2nd Material Code has gone.  Why would QlikView do this?

       

      I am clueless at the moment so any tips or fingers in the right direction will be a great help.

       

      Thanks in advance.

        • Re: Material codes are wrong once in QlikView
          John Cavoulas

          Sounds like you're manipulating the data in your load from SAP or during your save to QVD, possibly in your load from QVD. A straight forward set of instructions wouldn't do that, IMO. Can you share some of the code you're using?

            • Re: Material codes are wrong once in QlikView
              sean kaiser

              Sure... here is the code which saves to .QVD:-

               

              Material:   // Master Data: Characteristic Material
              Load
                
              [MATERIAL] as Material,
                 [EANUPC] as [EAN/UPC],
                
              [MATL_CAT] as MaterialCat,
                
              [MATL_GROUP] as MaterialGrp,
                
              [MATL_TYPE] as MaterialType,
                
              [VENDOR] as Vendor,
                 [/BIC/Z0022] as ProdHier1,
                
              [/BIC/Z0023] as ProdHier2,
                
              [/BIC/Z0024] as ProdHier3,
                
              [/BIC/Z0025] as ProdHier4,
                
              [/BIC/ZMM_MSTAE] as XPlantStatus;
              SQL Select MATERIAL
              EANUPC MATL_CAT MATL_GROUP MATL_TYPE PROD_HIER VENDOR /BIC/Z0022 /BIC/Z0023
              /BIC/Z0024 /BIC/Z0025 /BIC/ZMM_MSTAE
              from /BI0/PMATERIAL
              where OBJVERS = 'A'
              and /BIC/Z0022 <> ''
              ;
              STORE * FROM Material INTO $(vPathName)MATERIAL.QVD;
              DROP TABLE Material;

               

              Here is the code which loads into .QVW.  Currently there is nothing else in this QVW:-

               

              Material:

              LOAD Material,
                  
              [EAN/UPC],
                  
              MaterialCat,
                  
              MaterialGrp,
                  
              MaterialType,
                  
              Vendor,
                  
              ProdHier1,
                  
              ProdHier2,
                  
              ProdHier3,
                  
              ProdHier4,
                  
              XPlantStatus
              FROM
              $(vPathName)MATERIAL.QVD
              (
              qvd);

               

              The 2 records in question look like this in the source BW table so the WHERE clause is being met for both:-

              Untitled.png

               

              But in QlikView the E is dropped from one of them:-

              Capture.PNG

              It really has me puzzled.  I have dropped a few of the fields from the view in these screen shots, but basically the two records exist in BW with only 2 fields that have a different value... the Material Code (one ends in E) and the EAN/UPC field has a different value.  All other fields are the same value.

            • Re: Material codes are wrong once in QlikView
              sean kaiser

              OK - I've tried a few things with the SQL statement.

               

              If I use the following WHERE clause:-

               

              Where MATERIAL '60693' or MATERIAL = '60693E'

               

              I get the 2 Materials, but again the E is missing from one of them:-

               

              Capture.PNG

              But if I use this WHERE clause:-

               

              Where MATERIAL = '60693E'

               

              I get the 1 material with the E:-

               

              Capture.PNG

              I'm still baffled by this.  Why does the E get dropped when more than one record that begins 60693 is present?

               

              With reference to the table, it is just a standard Oracle table.  The cell contents can be copied into Notepad and the cell contents for this record are 60693E.