13 Replies Latest reply: Aug 10, 2010 10:59 PM by Trevor Keon RSS

    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.

        • SAPConnector loading BW floating point numbers
          s j

          Dear,

          Please Use

          num(Amount)

          I am already using it with BW.

          Regards

          Sunil Jain.

          • 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

              • SAPConnector loading BW floating point numbers
                Lars Wahlstedt

                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

                  • SAPConnector loading BW floating point numbers

                    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

                      • SAPConnector loading BW floating point numbers
                        Rakesh Mehta
                        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.
                        • SAPConnector loading BW floating point numbers
                          Rakesh Mehta

                          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.

                            • 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