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=22.214.171.124;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;"';
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.
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.