Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Material codes are wrong once in QlikView

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.

Tags (4)
1 Solution

Accepted Solutions
Employee
Employee

Re: Material codes are wrong once in QlikView

Try Text(Material) in the Load statement. QlikView probably interprets it as a number, and then duplicate formatting variants of the same number are removed.

HIC

9 Replies
johncaqc
Valued Contributor

Re: Material codes are wrong once in QlikView

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?

Not applicable

Re: Material codes are wrong once in QlikView

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.

Or
Valued Contributor II

Re: Material codes are wrong once in QlikView

Something I'd recommend checking - remove the DROP TABLE section of your QVD loader, and check the data as originally loaded into QV (before it enters a QVW) by viewing it there in a listbox. That should give you an idea of whether the correct data ever made its way into QV or not.

flipside
Valued Contributor II

Re: Material codes are wrong once in QlikView

Are you able to Find the two values in your QVD via Notepad (might need to test this with a smaller dataset) - this will confirm if the value got into your QVD or not.

flipside

Not applicable

Re: Material codes are wrong once in QlikView

Good idea!

I just did this and indeed the E has already been dropped when it comes out of BW.  So it is happening when i extract from BW using SQL.

I still dont know why it could happen though... below is proof that the E has gone directly after the SQL statement:-

Capture.PNG

Or
Valued Contributor II

Re: Material codes are wrong once in QlikView

Are you loading from the exact same table as you're viewing in your table viewer? Using the exact same query? Perhaps your "Table" is actually a view or query that appends the 'E' somehow. If you can pull the SQL out of the table viewer, you should be able to compare these.

Not applicable

Re: Material codes are wrong once in QlikView

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.

Employee
Employee

Re: Material codes are wrong once in QlikView

Try Text(Material) in the Load statement. QlikView probably interprets it as a number, and then duplicate formatting variants of the same number are removed.

HIC

Not applicable

Re: Material codes are wrong once in QlikView

It worked!  Thank you!

Community Browser