Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Bug in Qlikview 12 double records

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

Qlikview x64 12 bug1.png

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.


4 Replies
MVP
MVP

Re: Bug in Qlikview 12 double records

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

Could you post the document log?

Not applicable

Re: Bug in Qlikview 12 double records

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.)

MVP
MVP

Re: Bug in Qlikview 12 double records

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

Not applicable

Re: Bug in Qlikview 12 double records

Wow that i missed that. great thinking

converting PART1_PART to TEXT did the trick

Tanks man, really appreciate the help