Qlik Community

QlikView Connectors

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

SAPConnector loading BW floating point numbers

Hi,

I am trying to load data from a SAP BW system, and some of the fields I am trying to load are floating point numbers (FLTP). In BW these numbers are displayed as you would expect for floats:

3.0000000000000000E+03

When I try and load these into Qlikview, it is not treating them as numbers. I tried doing a to_number(xxx) on the SQL statement, however it returned an SQL error.

I am using the QVSAPConnector with QV version 8.5.

Any ideas on how I might be able to load these float values and be able to treat them as numbers in Qlikview? I do not have an issue if I lose some precision in the process.

1 Solution

Accepted Solutions
Not applicable

SAPConnector loading BW floating point numbers

Hi Rakesh,

When we upgraded to 5.4 we had issues loading Material Group data from BW. The error returned to Qlikview stated that there was an error in BW and check SM17 transaction for details. All it told us was there was an error, but didnt go into any detail at all!

So we went back to 5.2 as it was working (at the time) without any issues.

As a workaround I have added the following FLOAT_TEMP table to my loading script from BW:


MATL_PLANT:
LOAD replace(ltrim(replace([MAT_PLANT], '0', ' ')), ' ', 0) as [Material],
PLANT as [Plant],
ABCKEY as [ABC Indicator],
DISMM as [MRP Type],
[/BIC/GMRPGROUP] as [MRP Group],
MRP_CONTRL as [MRP Controller],
BASE_UOM as [Base UOM],
[/BIC/GLOTSIZE] as [Lot Size],
[/BIC/ZBSTMI] as Min_Lot_Temp,
[/BIC/ZBSTMX] as Max_Lot_Temp,
[/BIC/ZBSTFE] as Fixed_Lot_Temp;
SQL SELECT
PLANT,
MAT_PLANT,
ABCKEY,
DISMM,
/BIC/GMRPGROUP,
MRP_CONTRL,
BASE_UOM,
/BIC/GLOTSIZE,
/BIC/ZBSTMI,
/BIC/ZBSTMX,
/BIC/ZBSTFE
FROM /BI0/PMAT_PLANT
WHERE OBJVERS = 'A';
FLOAT_TEMP:
LOAD Material,
Plant,
Num(Mid(Min_Lot_Temp, 1, Len(Min_Lot_Temp)-4)) * pow(10, Num(Right(Min_Lot_Temp, 2))) as [Minimum Lot Size],
Num(Mid(Max_Lot_Temp, 1, Len(Max_Lot_Temp)-4)) * pow(10, Num(Right(Max_Lot_Temp, 2))) as [Maximum Lot Size],
Num(Mid(Fixed_Lot_Temp, 1, Len(Fixed_Lot_Temp)-4)) * pow(10, Num(Right(Fixed_Lot_Temp, 2))) as [Fixed Lot Size]
RESIDENT MATL_PLANT;

LEFT JOIN (MATL_PLANT)
LOAD Material,
Plant,
[Minimum Lot Size],
[Maximum Lot Size],
[Fixed Lot Size]
RESIDENT FLOAT_TEMP;
DROP Table FLOAT_TEMP;
DROP Fields Min_Lot_Temp, Max_Lot_Temp, Fixed_Lot_Temp;


It basically pulls the floating point number apart and does the calcuation to convert it to a number. It then puts it back into the main table I am loading into before storing it into a QVD file. This added about 50s to the loading time (from 1min 10sec to 2min) - which isnt too bad.

The fix does assume that the floating point number will always finish with "E+xx", which it does for the data I have reviewed. I could have used Index to find E+ and get everything after that, but it would add more processing time when I am trying to reduce it when possible.

This will have to do until I get more back from Qliktech. I have a feeling they will recommend what you have, Rakesh and Lars; upgrade to 5.4.

Regards,

Trevor

13 Replies
suniljain
Honored Contributor

SAPConnector loading BW floating point numbers

Dear,

Please Use

num(Amount)

I am already using it with BW.

Regards

Sunil Jain.

Not applicable

SAPConnector loading BW floating point numbers

Hi Sunil,

Thanks for the reply. I did try that however I only get null values returned.

suniljain
Honored Contributor

SAPConnector loading BW floating point numbers

Dear ,

What is the actual format in BW ?. It can return null if and only if there is unknown value in that field.

I mean if it have non numeric data .

Regards

Sunil Jain.

Not applicable

SAPConnector loading BW floating point numbers

Hi Sunil,

Below is a screenshot from the BW transaction RSA1. The fields I am having the issues with are the Min and Max Lot Sizes.

As you can see below, these fields are floating point numbers:

As you can see these are custom fields, however I cant see why that would be making the output into QV any different.

Regards,

Trevor

disqr_rm
Valued Contributor III

SAPConnector loading BW floating point numbers

Try

num#(ValueField, '#,###') as Whatever

Hopefully that should work.

Not applicable

SAPConnector loading BW floating point numbers

Hi Rakesh,

Thanks for the reply. I thought the format parameter (being '#,###') was for the output of the number and had no impact on the input? In any case I did give it a try but still no luck Smiley Sad

I also tried using the num# function without the format parameter and had the same result.

Im starting to get worried that my only option will be to do an inline table to manually calculate the numbers. This is an option I want to avoid as it will be a massive performance hit, given the volume of data I will be dealing with.

Could this problem be a result of the number of digits that are being loaded into the num function? Not that I know of any way to reduce the number of digits that are being pulled out of the BW field!

Regards,

Trevor

Not applicable

SAPConnector loading BW floating point numbers

Hi All,

I had a bit of a play today with this problem by trying to replicate it in a non-BW loading environment. I created the following script:


SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
FLOAT_TABLE:
LOAD * INLINE [
DATA
'3,00000000000000000000E+03'
];
TRANS_DATA:
LOAD num(DATA) as Float_Value
RESIDENT FLOAT_TABLE;
SET ThousandSep=',';
SET DecimalSep='.';
FLOAT_TABLE_EN:
LOAD * INLINE [
DATA_EN
'3.00000000000000000000E+03'
];
TRANS_DATA_EN:
LOAD num(DATA_EN) as Float_Value_En
RESIDENT FLOAT_TABLE_EN;


This works fine and can handle either loading with Euro number formats or non-Euro formats. I thought that this was the fix to the problem, as our BW system by default will display in the Euro format.

Unfortunately even when I applied the learnings from the above script (being to set the decimal separator correctly) it still had no effect. I even tried to apply the decimal separator logic when loading from the QVD the data is loaded into from BW. Nothing seems to work!

I even tried the following, probably more in desparation!


MATL_PLANT:
LOAD Material,
Plant,
[ABC Indicator],
[MRP Type],
[MRP Group],
[MRP Controller],
[Base UOM],
[Lot Size],
Text([Minimum Lot Size]) as [Minimum Lot Size],
Text([Maximum Lot Size]) as [Maximum Lot Size],
Text([Fixed Lot Size]) as [Fixed Lot Size]
FROM D:\Qlikview_QVD\BW\MD\BPP\QVD\0MATL_PLANT.qvd (qvd);
LEFT JOIN (MATL_PLANT)
LOAD Material,
Num([Minimum Lot Size],'#,##',',','.') as [Min Lot Size],
Num([Maximum Lot Size]) as [Max Lot Size],
Num([Fixed Lot Size]) as [Fix Lot Size]
RESIDENT MATL_PLANT;


I get the following result:

As you can see, the text versions show fine, but it cannot seem to handle the number conversion, even though when I manually set it using an inline statement it can convert it without any issues.

Any advice would be very much appreciated!

Regards,

Trevor

disqr_rm
Valued Contributor III

SAPConnector loading BW floating point numbers

Something is wrong, may be you should open a support call with QlikTech.

I tried same with R/3 VBAP table and I get correct results with VBAP-ABGES contents, which has same data element as yours - FLTP. The following script returns me correct values:



VBAP:
LOAD VBELN, num(ABGES, '#,##0') as ABGES;
SQL Select VBELN, ABGES from VBAP
WHERE VBELN = '0000160184';


Try opening a ticket with QlikTech.

Employee
Employee

SAPConnector loading BW floating point numbers

Hi

what version of the Connector are you using? There was a problem with FLTP in 540 IR, if you are using this you should upgrade to 540 SR1.

Regards, Lars Wahlstedt

Community Browser