Reload problem after update QV from v.10 SR4 to v.11.2

[#1]

Hi all,

after updating QlikView, I encountered a strange reload problem.

Specifically, the function involved is the following:

Table1:
LOAD
     MaxString(Column1)
RESIDENT Tablex
WHERE IsNull(Column2)=-1 OR Column2 >= '100'
GROUP BY Column4, Column5

Running this script with version 10, I get this (by log):

19/07/2013 11.24.27: 0370      Geografia:
19/07/2013 11.24.27: 0371                          LOAD
19/07/2013 11.24.27: 0372                                    MaxString(Column1) as Column1
19/07/2013 11.24.27: 0373                          RESIDENT Tablex
19/07/2013 11.24.27: 0374                          WHERE IsNull(Column2)=-1 OR Column2>= '100'
19/07/2013 11.24.27: 0375                          GROUP BY Column4, Column5
19/07/2013 11.24.27:                     1 fields found: Column1, 6.802 lines retrieved

Instead, running this script with version 11.02, I get this (by log):

19/07/2013 11.24.27: 0370      Geografia:
19/07/2013 11.24.27: 0371                          LOAD
19/07/2013 11.24.27: 0372                                    MaxString(Column1) as Column1
19/07/2013 11.24.27: 0373                          RESIDENT Tablex
19/07/2013 11.24.27: 0374                          WHERE IsNull(Column2)=-1 OR Column2>= '100'
19/07/2013 11.24.27: 0375                          GROUP BY Column4, Column5
19/07/2013 11.24.27:                     1 fields found: Column1, 6.650 lines retrieved

The source database is obviously the same, it isn't changed.

I tried searching about function MaxString(), but for now I haven't found anything.

Can someone help me?

Thanks in advance.

[Anonymous]

I'm out of ideas ... 😞

If you could upload both versions, I maybe able to help, but not sure in this case.

[#3]

This is really, really strange.

I made ​​the query as simple as possible:

Table1:
LOAD
     Max(Column1) as Column1
RESIDENT Tablex
GROUP BY Column4, Column5

The result is: 9.073 with version 11.02; 9.349 lines with 10 SR4

[pgrenier]

Just by curiosity, the original 63,102 records, have they been loaded using the exact same connectors?

The reason I'm asking this question is that, on a certain project, we realised that moving from an ODBC connector to a OLE DB connector on a AS/400 database slightly changed the way the data was loaded. In fact, the OLE DB connector would trim the trailing spaces in fields, while the ODBC didn't.

You can imagine that doing a grouped MaxString() on 'Abc     ' and 'Abc' would give you two different records with the ODBC connector, while you would get only one through the use of the OLE DB connector.

Just a thought...

Regards,

Philippe

[#5]

Philippe, thank you for your interest

I checked for safety, the connector is in both cases OLEDB.

[Anonymous]

Maybe save the Tablex table as QVDs in both versions and then analyse for differences using the EasyQlik QVD viewer (http://www.easyqlik.com/). This might show you whether fields are being saved with different uniqueness, density, record counts etc.

Jonathan

[#7]

I took your suggestion to use EasyQlik QVD viewer to analyze the Tablex table as QVDs in both versions.

Your suggestion was really helpful.

Now I want to show the results that I got.

Small clarification: the real name of Tablex is "dbo.AFF", the real name of Column1 is "IDAFF".
The column "IDAFF" is a Primary Key, declared as "char(6)" beause it contains unique hexadecimal values.

Picture 1:

Query on database in SQL Server Management Studio (I considered only a part of the column "IDAFF" values)

Picture 2:

-- LOAD DATA QLIKVIEW v.10 --

The QlikView load script is:

AFF:
     SQL SELECT *
     FROM dbo.AFF
     WHERE IDAFF like '0000D%';
STORE AFF INTO $(vQvd01Path)\AFF.qvd;

After loading, i opened AFF.qvd with EasyQlik Viewer and i got this result, correct i think.

Unique value 16.

Uniqueness 100% (ok, because all are PK)

Text Count 16
Numeric Count 0

Picture 3:

-- LOAD DATA QLIKVIEW v.11.02 --

The QlikView load script is exactly the same:

AFF:
     SQL SELECT *
     FROM dbo.AFF
     WHERE IDAFF like '0000D%';
STORE AFF INTO $(vQvd01Path)\AFF.qvd;

After loading, i opened AFF.qvd with EasyQlik Viewer and i got this result.

Unique value 7.

Uniqueness 43,75%.

Text Count 6
Numeric Count 10

Why different values? Is there a bug in QlikView?

[Anonymous]

Interesting...  Looks like QV10 and QV11 work with the hex data differently.  Maybe QV 11 reads it as decimal or binary, and because of length "rounds" it (?)

Can you create a list box with IDAFF field and show frequency (it is a checkbox on General tab)?  You'll see which values were loaded as non-unique.

[#9]

Here is the list box with IDAFF value and related frequency:

[Anonymous]

Not exactly what I wanted...  Can you load only this IDAFF field, and nothing else?  Preferably the same 16 values only:

WHERE IDAFF like '0000D%'

[Anonymous]

No need, it was easy to reproduce using the same data in text file.  It does work differently in 10 and 11.

To fix, use text() function

AFF:
     SQL SELECT

         ...,

                    text(IDAFF) as IDAFF

     FROM dbo.AFF
     WHERE IDAFF like '0000D%';