Re: Bug in Qlikview 12 double records - Qlik Community

Report Inappropriate Content · ‎2017-05-19

HI All,

im encountering some strange behavior in qlikview 12 and i cannot find whats wrong.

SELECT

PART1_PART,

COUNT(PART1_PART) AS 'COUNT'

FROM PART_DATA

WHERE PART1_REC_TYPE = '1'

AND PART1_CO_SITE = '20'

GROUP BY PART1_PART

HAVING COUNT(PART1_PART) > 1

returns (0 row(s) affected) in SQL Management studio

when i use this script

PART:

LOAD

RecNo() AS RecNo,

RowNo() AS RowNo,

PART1_PART

;

SQL SELECT DISTINCT

PART1_PART

FROM PART_DATA

WHERE PART1_REC_TYPE = '1' AND PART1_CO_SITE = '20';

now i get 77 doubled rows (out of 78.736 records)

this is not even possible with DISTINCT SQL query lvl

is this a known bug of qlikview 12 ?

EDIT: added logfile content

2017-05-19 14:33:59 Execution started.

2017-05-19 14:33:59 QlikView Desktop Version 12.10.20100+2017-02-16 09:28:18.HEAD.Component.Version

2017-05-19 14:33:59 CPU Target x64

2017-05-19 14:33:59 Operating System Windows 10 Pro (64 bit edition)

2017-05-19 14:33:59 Wow64 mode Not using Wow64

2017-05-19 14:33:59 Language 0413 Nederlands (Nederland) Dutch

2017-05-19 14:33:59 Country 31 Nederland Netherlands

2017-05-19 14:33:59 MDAC Version 6.3.9600.16384

2017-05-19 14:33:59 MDAC Full Install Version 6.3.9600.16384

2017-05-19 14:33:59 PreferredCompression 2

2017-05-19 14:33:59 EnableParallelReload 1

2017-05-19 14:33:59 ParallelizeQvdLoads 1

2017-05-19 14:33:59 AutoSaveAfterReload 0

2017-05-19 14:33:59 BackupBeforeReload 1

2017-05-19 14:33:59 EnableFlushLog 0

2017-05-19 14:33:59 SaveInfoWhenSavingFile 0

2017-05-19 14:33:59 UserLogfileCharset 65001

2017-05-19 14:33:59 OdbcLoginTimeout -1

2017-05-19 14:33:59 OdbcConnectionTimeout -1

2017-05-19 14:33:59 LongestPossibleLine 1048576

2017-05-19 14:33:59 MaxCoreMask -1

2017-05-19 14:33:59 ScriptWantsDbWrite false

2017-05-19 14:33:59 ScriptWantsExe false

2017-05-19 14:33:59 LogFile CodePage Used: 65001

2017-05-19 14:33:59 UseMemoryFillPattern 1

2017-05-19 14:33:59 ScriptMode Legacy (gives greater system access) (aka qv11 mode)

2017-05-19 14:33:59 SequentialThreadCount 34

2017-05-19 14:33:59 NumberOfCores (effective) 4

2017-05-19 14:33:59 ReloadCodebase Classic

2017-05-19 14:33:59 Reload Executed By *****\marco

2017-05-19 14:33:59 Process Executing QlikView Desktop

2017-05-19 14:33:59 Process ID 25176

2017-05-19 14:33:59 0002 SET ThousandSep='.'

2017-05-19 14:33:59 0003 SET DecimalSep=','

2017-05-19 14:33:59 0004 SET MoneyThousandSep='.'

2017-05-19 14:33:59 0005 SET MoneyDecimalSep=','

2017-05-19 14:33:59 0006 SET MoneyFormat='€ #.##0,00;€ -#.##0,00'

2017-05-19 14:33:59 0007 SET TimeFormat='hh:mm:ss'

2017-05-19 14:33:59 0008 SET DateFormat='DD-MM-YYYY'

2017-05-19 14:33:59 0009 SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]'

2017-05-19 14:33:59 0010 SET FirstWeekDay=0

2017-05-19 14:33:59 0011 SET BrokenWeeks=0

2017-05-19 14:33:59 0012 SET ReferenceDay=4

2017-05-19 14:33:59 0013 SET FirstMonthOfYear=1

2017-05-19 14:33:59 0014 SET CollationLocale='nl-NL'

2017-05-19 14:33:59 0015 SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec'

2017-05-19 14:33:59 0016 SET LongMonthNames='januari;februari;maart;april;mei;juni;juli;augustus;september;oktober;november;december'

2017-05-19 14:33:59 0017 SET DayNames='ma;di;wo;do;vr;za;zo'

2017-05-19 14:33:59 0018 SET LongDayNames='maandag;dinsdag;woensdag;donderdag;vrijdag;zaterdag;zondag'

2017-05-19 14:33:59 0020 OLEDB CONNECT TO *** (XPassword *** ***)

2017-05-19 14:33:59 0022 PART:

2017-05-19 14:33:59 0023 LOAD

2017-05-19 14:33:59 0024 RecNo() AS RecNo,

2017-05-19 14:33:59 0025 RowNo() AS RowNo,

2017-05-19 14:33:59 0026 PART1_PART

2017-05-19 14:33:59 0027

2017-05-19 14:33:59 0028 SQL SELECT DISTINCT

2017-05-19 14:33:59 0029 PART1_PART

2017-05-19 14:33:59 0030

2017-05-19 14:33:59 0031 FROM PART_DATA

2017-05-19 14:33:59 0032 WHERE PART1_REC_TYPE = '1' AND PART1_CO_SITE = '20'

2017-05-19 14:33:59 0033

2017-05-19 14:34:00 3 fields found: RecNo, RowNo, PART1_PART,

2017-05-19 14:34:00 78.736 lines fetched

2017-05-19 14:34:00 0037 Doubled:

2017-05-19 14:34:00 0038 NoConcatenate

2017-05-19 14:34:00 0039 LOAD

2017-05-19 14:34:00 0040 PART1_PART AS PART1_PART_DOUBLED,

2017-05-19 14:34:00 0041 Count

2017-05-19 14:34:00 0042 WHERE Count > 1

2017-05-19 14:34:00 0043

2017-05-19 14:34:00 0044 LOAD

2017-05-19 14:34:00 0045 PART1_PART,

2017-05-19 14:34:00 0046 COUNT(PART1_PART) AS Count

2017-05-19 14:34:00 0047 RESIDENT PART GROUP BY PART1_PART

2017-05-19 14:34:00 2 fields found: PART1_PART_DOUBLED, Count,

2017-05-19 14:34:00 77 lines fetched

2017-05-19 14:34:01 Execution finished.

swuehl · ‎2017-05-19

Is this single LOAD statement for PART table your complete script?

Could you post the document log?

Report Inappropriate Content · ‎2017-05-19

Yes,

i had this larger script where i noticed strange values... after some time fiddling around and not getting the correct expected results i created a new qlikview (isolated) document with a single load trying to find my own mistake.

but then i discovered that there where doubled lines

(PS: i have added the document log, after i have created a group by table, so you can see the doubled rows.)

swuehl · ‎2017-05-19

I've noticed that your values show leading zeros and no leading zeros.

Could it be that a doubled value is in fact showing the same numeric value in two different formats in your source system?

Note that Qlik can only store a single text representation per numeric value.

Try

PART:

LOAD

RecNo() AS RecNo,

RowNo() AS RowNo,

Text(PART1_PART) as PART1_PART

;

SQL SELECT DISTINCT

PART1_PART

FROM PART_DATA

WHERE PART1_REC_TYPE = '1' AND PART1_CO_SITE = '20';

Report Inappropriate Content · ‎2017-05-19

Wow that i missed that. great thinking

converting PART1_PART to TEXT did the trick

Tanks man, really appreciate the help