Field is empty when I do a group by on a .qvd

[Anonymous]

Hi!

Just send the following case to QT-Support. I wasn't aware that a group by on a .qvd can be that dangerous. Hopefully they at least update the documentation on this.

The following simple script returns an empty field "Key" when I do a group by on a .qvd.

- I'm aware of the knowledgebase article https://eu1.salesforce.com/articles/Basic/Transform-durring-QVD-load?popup=true

- I'm aware of the workaround going through a resident load

HOWEVER: nowhere in the documentation is written that transformations on .qvds are not allowed.

The "qvd Files" entry in the help only states "QVD files can be read in two modes: standard (fast) and optimized (faster). The selected mode is determined automatically by the QlikView script engine. Optimized mode can be utilized only when all loaded fields are read without any transformations (formulas acting upon the fields), though the renaming of fields is allowed. A WHERE clause causing QlikView to unpack the records will also disable the optimized load."

--> please fix this erroneous behavior for "standard mode .qvd load", or at least add a documentation which statements are allowed to read .qvds correctly in "standard mode".

tst:

load

floor(rowno()/2) as key1,

rowno()*2 as key2,

rand() as value

AutoGenerate(100000);

store tst into tst.qvd;

drop table tst;

load

key1&'|'&key2 as Key,

sum(value)

from tst.qvd(qvd)

where key1 > 5000

group by key1,key2;

[maxgro]

try this

load

key1&'|'&key2 as Key,

sum(value)

from tst.qvd(qvd)

where key1 > 5000

group by key1&'|'&key2;

usually group by is with all non aggregate fields; it seems that

grouping by key1, key2

and

grouping by key1&'|'&key2

gives different result

[rbecher]

I think the reason is that the group by is made on the fields of the resulting table, not the source table. So, it's different than in SQL.

[Anonymous]

havent heard back yet from QTSupport, hope they add this to the documentation!

@massimo: nice workaround; could be faster than doing the groupby on a resident-load, but imho  those are all just workarounds on this issue..

[rbecher]

I don't think it's an issue...

[jaimeaguilar]

Hi,

I also don't think this is an issue, besides what Massimo proposes is not really a workaround, but the correct way to do this group by. This is similar to reuse a formula in script for a new field, when you don't want to use preceding load for example:

Table1:

LOAD Field1,

          Field2,

          Field1 & '|' & Field2 as Field3,

          Field1 & '|' & Field2 & '|' & Field3 as Field4 //Here you can't call/reuse "Field3" for Field4 calculation, because you're at the same level. if you want to use "Field3" in other fields calculations you may use preceding load. It happens the same with your group by.

regards

[Anonymous · Accepted solution]

Can't aggree - I think it's definitely a specific issue with .qvd files;

from a SQL-perspective my original statement is valid.

Also QlikView calculated correctly when I go through a resident Load:

Tmp:

load

*

from tst.qvd(qvd);

Result:

load

key1&'|'&key2 as Key,

sum(value)

resident Tmp

where  key1 > 5000

group by key1,key2;

I also got feedback from QT Support providing me the ID 68229 for a documentation bug. So looking forward to the next SRs how his gonna end up in the documentation!

[rbecher]

..but it isn't a SQL query.

[Anonymous]

I know; but Ralf why do you take Qlik under your wings?

A QV-LOAD statement looks like an SQL statement, typically it behaves like a SQL statement and nowhere in the documentation it's written that my group-by statement on a .qvd is not valid. Any QlikView novice would wonder about this behavior. We might know that .qvd is kind of special, but without this knowledge it's a flaw:

Script execution puts no error, no warning (because they don't exist) and simply returns a unforseeable result.

--> to me this is a (documentation) bug.

[rbecher]

Which wings? 😉

I think we have to learn and understand how QlikView is working. And some processings are different than SQL because it's procesed on the target, not source.

So I do agree, it's a documentation issue.