SAPConnector loading BW floating point numbers

[#1]

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.

[#2]

Hi Rakesh,

I agree something is wrong, given what I have discovered so far it doesnt quite add up for me. Have raised a ticket to Qliktech as you suggested. Was hoping this would be a problem someone else had come across so I didnt have to bother Qliktech (im sure they get a lot of questions that have been answered here before!).

Hi Lars,

We are using release version 5.2 SR1. The full version number is 5.2.7058.0. We did attempt an update to 5.4 recently but it was rolled back due to some issues that surfaced. I will find it ironic if 5.4 would fix my problem as it raises a whole bunch of new ones! 🙂

Thanks for the replies so far guys, I feel like I am making progress!

Regards,

Trevor

[disqr_rm]

Hi Trevor, I think you should upgrade again to 5.4 as suggested by Lars, and work with QlikTech or here in community to solve/workaround problems you have with new version. This could be easier than fixing problems in old versions. By the way, what problems did you get with new version? I am using the newest and did not encounter anything yet.

[disqr_rm]

Hi Trevor, I think you should upgrade again to 5.4 as suggested by Lars, and work with QlikTech or here in community to solve/workaround problems you have with new version. This could be easier than fixing problems in old versions. By the way, what problems did you get with new version? I am using the newest and did not encounter anything yet.

[#5 · Accepted solution]

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