topic Re: Material codes are wrong once in QlikView in QlikView

Material codes are wrong once in QlikView

Wed, 19 Dec 2012 13:20:46 GMT

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 Code	Created On	EAN/UPC Code
60693	05.11.2004	7613413
60693E	05.11.2004	7614780

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 Code	Created On	EAN/UPC Code
60693	05.11.2004	7613413
60693	05.11.2004	7614780

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

johnca — Wed, 19 Dec 2012 13:39:02 GMT

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

Wed, 19 Dec 2012 13:52:12 GMT

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

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

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

Or — Wed, 19 Dec 2012 13:58:43 GMT

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.

Re: Material codes are wrong once in QlikView

flipside — Wed, 19 Dec 2012 14:09:41 GMT

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

Re: Material codes are wrong once in QlikView

Wed, 19 Dec 2012 14:19:13 GMT

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

Re: Material codes are wrong once in QlikView

Or — Wed, 19 Dec 2012 15:09:44 GMT

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.

Re: Material codes are wrong once in QlikView

Wed, 19 Dec 2012 16:29:16 GMT

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

But if I use this WHERE clause:-

Where MATERIAL = '60693E'

I get the 1 material with the E:-

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.

Re: Material codes are wrong once in QlikView

hic — Wed, 19 Dec 2012 17:46:23 GMT

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

Re: Material codes are wrong once in QlikView

Thu, 20 Dec 2012 08:40:03 GMT

It worked! Thank you!