topic Re: Bug or misunderstanding? in QlikView

Bug or misunderstanding?

till_bentz — Tue, 13 May 2014 13:39:04 GMT

Hello,

I have question regarding the automatic concatenation of tables in QlikView.

Say we have the following code in a load script:

Table1_temp:

LOAD Value, Date;

SQL SELECT * FROM database;

Table1:

LOAD Value, Date Resident Table1_temp Order By Date asc;

DROP Table Table1_temp;

I had initially assumed that this would result in one Table1 with its records sorted by Date.

It turned out, that I was wrong. QlikView automatically concatenates the two tables into one Table called Table1_temp. This Table contains all records in the original order followed by the records ordered by Date.

It took some time to find my error. My question is:

Is this a bug? I would expect this behavior if I hadn't defined a new name for the table...

What do you think?

Just to clarify this post. I know how to avoid this behavior. I was just wondering, if I am the only one who expected it to be different

Regards

Till

Nachricht geändert durch Till Bentz

Re: Bug or misunderstanding?

alexandros17 — Tue, 13 May 2014 13:41:28 GMT

It is a normal behaviour, to avoid use noconcatenate:

Table1_temp:

LOAD Value, Date;

SQL SELECT * FROM database;

Table1:

noconcatenate

LOAD Value, Date Resident Table1_temp Order By Date asc;

DROP Table Table1_temp;

hope it helps

Re: Bug or misunderstanding?

ashfaq_haseeb — Tue, 13 May 2014 13:44:00 GMT

Try below code

Table1_temp:

LOAD Value, Date;

SQL SELECT * FROM database;

no concatenate

Table1:

LOAD Value, Date Resident Table1_temp Order By Date asc;

DROP Table Table1_temp;

Regards

ASHFAQ

Re: Bug or misunderstanding?

till_bentz — Tue, 13 May 2014 13:44:59 GMT

Hello,

thanks. I know it seems to be normal behavior. I also found the solution you suggested. But I still think it is at least confusing.

I purposefully assigned a new name to the table so I would expect, that it would not be concatenated automatically...

Re: Bug or misunderstanding?

simondachstr — Tue, 13 May 2014 13:45:24 GMT

Why should QlikView change the name of an existing table and redefine it? I would expect the behaviour to be the other way around.. once a table has been loaded, check if there's already a loaded table with the same fields. If yes, concatenate, if not, create a different table.

Re: Re: Bug or misunderstanding?

till_bentz — Tue, 13 May 2014 13:49:40 GMT

QlikView should "change" the name because I gave it the instruction to do so by adding Table1: in front of the Load statement (at least that is my understanding of the code )

If I want to concatenate it to an existing table with the same fields, I wouldn't define a name for it...

Re: Bug or misunderstanding?

alexandros17 — Tue, 13 May 2014 13:50:59 GMT

Even with a new name for the table qlik will concatenate, this because the fields list is the same, if you add another field in the second table this will not happen anymore

Re: Bug or misunderstanding?

Tue, 13 May 2014 13:52:22 GMT

Hello,also in this way, renaming the field in the 2nd load should not concatenate

Table1_temp:

LOAD Value, Date;

SQL SELECT * FROM database;

Table1:

LOAD Value, Date as NewDate Resident Table1_temp Order By Date asc;

DROP Table Table1_temp;

Re: Bug or misunderstanding?

its_anandrjs — Tue, 13 May 2014 13:54:37 GMT

Try to load like

Table1_temp:

LOAD Value, Date;

SQL SELECT * FROM database;

Noconcatenate

Table1:

LOAD Value, Date Resident Table1_temp Order By Date asc;

DROP Table Table1_temp;

Try to add a dummy field like 1 as Value then the situation different

Table1_temp:

LOAD Value, Date;

SQL SELECT * FROM database;

Table1:

LOAD Value, Date, 1 as Value Resident Table1_temp Order By Date asc;

DROP Table Table1_temp;

Normally when we load a table from any table as resident if there is same field name qlikview refers the previous table name and for new table it creates newname try to rename the field or you can use Noconcatenate then see the difference.

Hope this helps

Re: Re: Bug or misunderstanding?

simondachstr — Tue, 13 May 2014 13:55:10 GMT

That's true - the concatenation is being done automatically because if not, then you'll get a fantastic synthetic key containing a mix of all fields at the end (which would eventually crash at the end of the load).

Re: Bug or misunderstanding?

Anonymous — Tue, 13 May 2014 13:56:21 GMT

It is the expected behavior.

When you load a table with the exactly the same set of fields as a previously loaded table, it is auto-concatenated to the previously loaded table. It doesn't matter if you assign a ne label or not, or if there is a label at all. It doesn't matter when the first table was loaded - just before the second, or way before. The set of fields is only what matters.

As others has said, you can use NOCONCATENATE to avoid this.

In your case, you can use order in the first table.

Re: Bug or misunderstanding?

till_bentz — Tue, 13 May 2014 13:59:32 GMT

How can I order the first table? Whenever I tried to order directly in preceding loads I got errors with non existing fields or something like that...

Re: Bug or misunderstanding?

Anonymous — Tue, 13 May 2014 14:08:23 GMT

I don't think it works in the preceding load. This is what I mean:

Table1_temp:

LOAD Value, Date;

SQL SELECT Value, Date FROM database

ORDER BY Date;

Wondering why do you need order. Are you using it further in the script?

Re: Bug or misunderstanding?

Tue, 13 May 2014 14:08:34 GMT

Table1_temp:

LOAD Value, Date;

SQL SELECT * FROM DATABASE order by Date asc;

Re: Bug or misunderstanding?

cfz — Tue, 13 May 2014 14:20:49 GMT

Hi Till.

This is the expected behavior according to the Reference Manual and not a bug.

From the Reference Manual:

Each load or select statement generates a table. Normally, QlikView treats the result of each one of these as

one logical table. However, there are a couple of exceptions from this rule:

- If two or more statements result in tables with identical field names, the tables are concatenated and

treated as one logical table.

The NoConcatenate prefix forces two loaded tables with identical field sets to be treated as two separate

internal tables, when they otherwise would be automatically be concatenated.

I hope it helps,

Carlos

Re: Bug or misunderstanding?

till_bentz — Tue, 13 May 2014 14:20:55 GMT

Thanks.

Unfortunately I can't use the order by on SQL level, because in my real application I already have several preceding loads.

And yes, I need the order, because later on I rely on them being ordered by date to find specific values.

Re: Bug or misunderstanding?

till_bentz — Tue, 13 May 2014 14:24:10 GMT

Thank you!

I guess what I would have expected is, that naming a table would result in an implicit NoConcatenate...

Especially when you have long load scripts this might save you from confusions during development...