Extracting from SAP Table KNA1

[mpbtejada]

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.

[trm]

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

[mpbtejada]

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;"';

[trm]

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

[mpbtejada]

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.

[mpbtejada]

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.

[trm · Accepted solution]

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

[mpbtejada]

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?

[trm]

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

[mpbtejada]

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?