Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

oharab2000
New Contributor II

Is this a bug? Loading same fields with resident

Hi,

I'm still new to QlikView, so I'm not sure if this is a bug or by design.

I'm loading a QVD in, then I'm splitting the data out into separate QVDs:

tmpCrimes:

LOAD [Offence Reference],

     [Crime Category]

     FROM

[..\QVDs\QueryRunner\Crimes.qvd]

(qvd);

tmpActiveCrime:

LOAD *

Resident tmpCrimes

WHERE [Crime Category]='Other Theft'

;

However when I run this code, rather than having 2 tables (well 2 tables and a synthetic join) it just dumps the data into itself:

tmpCrimes << Crimes (qvd optimized) 499,785 lines fetched

tmpCrimes << tmpCrimes 609,027 lines fetched

If I alias one of the fields, it works fine, but then I don't get the replica data sets I was after.I know I can put a filter on the read from QVD, but then I lose out on the advantage of optimized reads.

Any thoughts?

Tags (2)
1 Solution

Accepted Solutions
jaimeaguilar
Valued Contributor II

Re: Is this a bug? Loading same fields with resident

Hi,

That is normal behaviour. What is happening is that both tables are auto-concatenating because they contain exactly the same fields (that's the way it works in QlikView).

A solution would be to add the line NOCONCATENATE before loading the second tables

regards

8 Replies
MCampestrini
Valued Contributor

Re: Is this a bug? Loading same fields with resident

Hi Ben!

If you load a table from a resident one, you must supply another field (ex. RowNo()) in the new table. If you don't do this, Qlikview assume the concatenation between the tables.

Márcio

Márcio Rodrigo Campestrini
jaimeaguilar
Valued Contributor II

Re: Is this a bug? Loading same fields with resident

Hi,

That is normal behaviour. What is happening is that both tables are auto-concatenating because they contain exactly the same fields (that's the way it works in QlikView).

A solution would be to add the line NOCONCATENATE before loading the second tables

regards

jvishnuram
Contributor III

Re: Is this a bug? Loading same fields with resident

Hi Ben,

Qlikview itself stores the unique set of values in the memory, so the script you have written is already stored in the qlikview memory, thats why you are getting an error.

Load with one additional field which is Rowno() or RecNo() or Autonumber(Any_Field).

Another Option is NOCONCATENATE.

Put this keyword infront of the load statement.

Re: Is this a bug? Loading same fields with resident

Use NOCONCATENATE before loading 2nd table...!

dagomezl
Contributor III

Re: Is this a bug? Loading same fields with resident

Hello,

when you load two tables with fields equals qlikview concatenate the two tables, to avoid this, you need add the function NOCONCATENATE

dfoster9
Valued Contributor

Re: Is this a bug? Loading same fields with resident

The NOCONCATENATE advice above is correct, but be careful that you dont leave these tables as-is as identical columns in multiple tables will cause a synthetic key to be created. If you data model has lots of identical columns then the size of synthetic key could be considerable and take a long time to calculate.

If you want the tables in your model then look at QUALIFY as a method of ensuring the columns remain unique to the table. (hint: use UNQUALIFY for the fields you do want to join on)

Not applicable

Re: Is this a bug? Loading same fields with resident

Hi,

Try this:

tmpCrimes:

LOAD [Offence Reference],

     [Crime Category]

     FROM

[..\QVDs\QueryRunner\Crimes.qvd]

(qvd);

tmpActiveCrime:

LOAD      *,

               1 as tmpField  // This field make different table 1 to table 2 and QV read as 2 differents tables, else your

                                        // "Where" applies for tmpCrimes and tmpActiveCrime

Resident tmpCrimes

WHERE [Crime Category]='Other Theft'

;

DROP Table tmpCrimes; // if you don't need this table any more

DROP Field tmpField; // to delite this field

Another option is:

tmpCrimes:

LOAD [Offence Reference],

     [Crime Category]

     FROM

[..\QVDs\QueryRunner\Crimes.qvd]

(qvd);

Qualify * ;

tmpActiveCrime:

LOAD *

Resident tmpCrimes

WHERE [Crime Category]='Other Theft'

;

UnQualify * ;

Best regards.

oharab2000
New Contributor II

Re: Is this a bug? Loading same fields with resident

NoobieError!!

Thanks all. NoConcatenate has solved  this. David Foster: Thanks for the warning, but in this case all I am doing is creating a range of smaller, more specific QVDs.

Community Browser