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

Is this a bug? Loading same fields with resident

oharab2000 — Mon, 21 Jul 2014 14:32:40 GMT

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?

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

Marcio_Campestrini — Mon, 21 Jul 2014 14:34:59 GMT

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

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

jaimeaguilar — Mon, 21 Jul 2014 14:37:02 GMT

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

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

jvishnuram — Mon, 21 Jul 2014 14:38:14 GMT

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

MK_QSL — Mon, 21 Jul 2014 14:44:04 GMT

Use NOCONCATENATE before loading 2nd table...!

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

Anonymous — Mon, 21 Jul 2014 14:44:41 GMT

Hello,

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

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

DavidFoster1 — Mon, 21 Jul 2014 14:47:12 GMT

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)

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

Mon, 21 Jul 2014 14:48:38 GMT

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.

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

oharab2000 — Mon, 21 Jul 2014 15:05:43 GMT

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.