Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

9 Replies
johnca
Specialist
Specialist

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
Author

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
MVP
MVP

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
Partner - Specialist II
Partner - Specialist II

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
Author

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
MVP
MVP

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
Author

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.

hic
Former Employee
Former Employee

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
Author

It worked!  Thank you!