Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
oharab2000
Contributor II
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?

1 Solution

Accepted Solutions
jaimeaguilar
Partner - Specialist II
Partner - Specialist II

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

View solution in original post

8 Replies
Marcio_Campestrini
Specialist
Specialist

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
Partner - Specialist II
Partner - Specialist II

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
Partner - Creator III
Partner - Creator III

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.

MK_QSL
MVP
MVP

Use NOCONCATENATE before loading 2nd table...!

dagomezl
Creator III
Creator III

Hello,

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

DavidFoster1
Specialist
Specialist

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

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
Contributor II
Contributor II
Author

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.