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:
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.
Solved! Go to Solution.
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:
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;
WHERE OBJVERS = 'A';
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]
LEFT JOIN (MATL_PLANT)
[Minimum Lot Size],
[Maximum Lot Size],
[Fixed Lot Size]
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.
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 .
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.
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
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!
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 TimeFormat='h:mm:ss TT';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
LOAD * INLINE [
LOAD num(DATA) as Float_Value
LOAD * INLINE [
LOAD num(DATA_EN) as Float_Value_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!
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)
Num([Minimum Lot Size],'#,##',',','.') as [Min Lot Size],
Num([Maximum Lot Size]) as [Max Lot Size],
Num([Fixed Lot Size]) as [Fix Lot Size]
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!
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:
LOAD VBELN, num(ABGES, '#,##0') as ABGES;
SQL Select VBELN, ABGES from VBAP
WHERE VBELN = '0000160184';
Try opening a ticket with QlikTech.
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