10 Replies Latest reply: Oct 20, 2017 10:20 AM by Thomas Örnmarker RSS

    Extracting from SAP Table KNA1

    Marvin Tejada

      Hi,

       

      We recently upgraded from SAP Connector 5.4 to the latest 6.5.

      Main change was the treatment of fields that accept both numeric and string entries.

      We changed the connection string parameter MixedMode to TRUE and this made the transition for most of our built extractors bearable.

       

      Except for 1 so far. Extracting from SAP table KNA1.

      For some reason under MixedMode TRUE, customer code (KUNNR) P434 and 434 is treated as the same.

      I tried changing MixedMode to FALSE and this separated the values.

       

      Can anyone please give clarification?

      Thanks.

        • Re: Extracting from SAP Table KNA1
          Thomas Örnmarker

          Hi Marvin,

          I've tried reproducing your issue but did not get the same result.

          We don't have the values P434 & 434 in our KNA1 table but I tried with another table where we had a CHAR10 field defined and added two rows; one with 434 and one with P434

          They are always presented as two values, the only difference being that 434 is right aligned with MixedMode=true and left aligned when MixedMode=false (expected outcome).

          I left all the other connection string properties to their default values.

           

          To investigate further I would need more information such as log files and script samples.

           

          Regards,

          // Thomas Örnmarker

          SAP Connector developer

            • Re: Extracting from SAP Table KNA1
              Marvin Tejada

              Hi,

               

              THe script is as plain as this:

               

              SQL Select KUNNR LIFNR ERDAT ANRED LAND1 NAME1 NAME2 NAME3 NAME4 REGIO WERKS LZONE KTOKD KONZS LOEVM KATR2 from KNA1;

               

              This is my connection string:

               

              SET vSAPConn_PROD = '"Provider=QvSAPSqlConnector.exe;servertype=0;ASHOST=150.200.3.46;CLIENT=700;SYSNR=00;Timeout=3600;SNC_MODE=false;SNC_QOP=9;advancedProperties=false;ConvRoutine=true;CheckSeparator=false;Nulldate=true;RemoveAllBlanks=false;ReplaceNullvalue=true;Trace=false;MixedMode=true;NulldateLegacy=false;FLTPLegacy=false;TIMSLegacy=true;DataTypeLegacyMode=true;PacketSize=20000;FetchBuffers=99999999;BufferPercentage=10;TimeOutBatch=600;TimeOutFetch=1200;TimeOutStartBatch=2400;BatchJobName=/QTQVC/READ_DATA;SocketBufferSize=32768;SenseVersion=12.10.20600+2017-08-11 08:59:13.HEAD.Component.Version;XUserId=ZFPTXYRORTdGWQNMDLZGXZdR;XPassword=aJKYBYFMTbcOXXJOELJETYRNRZaMXYMGXLJCTUB;"';

                • Re: Extracting from SAP Table KNA1
                  Thomas Örnmarker

                  Thanks for the connection string and select script. I tried with changing my connection string to reflect yours, but didn't change the outcome.

                   

                  I can't think of anything that should be able to to what you describe. When MixedMode=true we just forward the data fetched from SAP to Qlik and doesn't specify any datatype, that is left for Qlik to decide by inspecting the retrieved data.

                   

                  What you perhaps could try would be to run the connector standalone (launching it from explorer) and selecting the KUNNR and some more fields from the KNA1 table and then clicking OK. This will generate a qvx file that you can import into QlikView by clicking Table Files..., if you still get the same behaviour then I would very much like to have a look at the qvx file. (Note that you need to specify connection string properties in the connect dialog when launching connector standalone, and specifically checking MixedMode as it defaults to false.) If you don't get the same behaviour then we at least know that and can draw conclusions based on that.

                   

                  Regards,

                  // Thomas Örnmarker

                  SAP Connector developer

                    • Re: Extracting from SAP Table KNA1
                      Marvin Tejada

                      I tried creating a new QVW file, used the same connection string and script and it's now ok.

                      I've noticed too that the default SET statements at the beginning have increased.

                      Do these have anything to do with the extracts?

                       

                       

                      SET ThousandSep=',';

                      SET DecimalSep='.';

                      SET MoneyThousandSep=',';

                      SET MoneyDecimalSep='.';

                      SET MoneyFormat='$#,##0.00;($#,##0.00)';

                      SET TimeFormat='h:mm:ss TT';

                      SET DateFormat='M/D/YYYY';

                      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

                      SET FirstWeekDay=6;

                      SET BrokenWeeks=1;

                      SET ReferenceDay=0;

                      SET FirstMonthOfYear=1;

                      SET CollationLocale='en-US';

                      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                       

                      I tried running the old extractor and it really is giving the wrong results.

                        • Re: Extracting from SAP Table KNA1
                          Marvin Tejada

                          This is what I've done so far:

                           

                          1. Used the newly added SET statements to the old script. -> FAIL
                          2. Created a new file and copied the old script. -> OK
                          3. Created a blank file, referenced the file to the current PRJ folder and reloaded. -> FAIL
                          4. Removed referenced PRJ folder and reloaded only QVW file. ->FAIL

                           

                          For your reference, I've uploaded 2 files for comparison. KNA1 Current.qvw came from the existing extractor with the wrong output while KNA1 Test.qvw is the new file I created with the correct output.

                            • Re: Extracting from SAP Table KNA1
                              Thomas Örnmarker

                              Okay I now can reproduce your issue!

                              The reason is the definition of the MoneyFormat used in the old script KNA1 Current.qvw:

                              SET MoneyFormat='Php #,##0.00;(Php #,##0.00)';

                              If you change it to what's being used in the working qvw you will see that the old qvw will start working as expected:

                              SET MoneyFormat='$#,##0.00;($#,##0.00)';

                               

                              I don't know the details of the MoneyFormat pattern string but the Php in the beginning what was catched my eye as you were having problems with values starting with 'P'.

                               

                              // Thomas Örnmarker

                                • Re: Extracting from SAP Table KNA1
                                  Marvin Tejada

                                  Whoa! Indeed that corrected the issue!

                                  Now what symbol to use instead for our Philippine currency...

                                   

                                  Anyways, so is this a Qlikview 12 issue more than a SAP Connector issue then?

                                    • Re: Extracting from SAP Table KNA1
                                      Thomas Örnmarker

                                      Well I would rather say a side effect of how Qlik works in combination with MixedMode=true and your MoneyFormat string, because when MixedMode=true we send the data marked as UNKNOWN to Qlik (for datatypes Numeric Text and Text) which means it up to Qlik to decide what the values actually are, and one of the format it proofs is the money format which it gets a match for as the value in this case correspond to how a money value should look like, so it treats the value as a numeric one, and when the next value with the same numeric value (434) comes in it is treated the same and they are presented using the text value it first parsed (Either 434 or P434, whichever comes in first).

                                       

                                      When MixedMode=false, we mark the data sent to Qlik with ASCII and it will not try to decide the type because it's already told its ascii text.

                                       

                                      Hopefully I could shed some more light on the issue.

                                       

                                      // Thomas Örnmarker

                                        • Re: Extracting from SAP Table KNA1
                                          Marvin Tejada

                                          Thanks for the explanation. Kinda clears it up.

                                          Going back to our old extractors, I have a few questions up my sleeve:

                                           

                                          1. Is it correct to say that the SAP data we've previously stored in our QVDs are all defined as Unknown?
                                          2. If it is indeed Unknown, would Qlikview be confused when we concatenate the old Undefined data with new data that is defined when MixedMode =  false?
                                          3. What would you think would be the best and most efficient way to align all these moving forward?
                                          4. Would Num() on extract correct the data format in our load scripts?
                                            • Re: Extracting from SAP Table KNA1
                                              Thomas Örnmarker

                                              1. Yes I believe they are, haven't checked for all possible data types though.

                                              2. I haven't tried this out myself so no good answer there, needs experimenting...

                                              3. One thing you could try is to test an application named EasyQlik QViewer, it's not a Qlik product but I've found it convenient for looking at QVD files and what different metadata is assigned on different levels. You can see the unknown flag in the XML Type metadata field.

                                               

                                              But I don't think using MixedMode=true is a bad thing, if you have fields with mixed content it makes a lot of sense using it (and utilizing Qlik's 'intelligence'). But it can be too 'broad', i.e what you saw in detecting values as money. So if you want better control and know for example that there's only text or numeric values you could use MixedMode=false and trying Num#() function on the values and falling back on text.

                                               

                                              4. Yes but probably hard to cover for all different probing that Qlik does (i.e numeric, money, dates, times, etc.) so depending on your situation.

                                               

                                              // Thomas Örnmarker