Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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.
Use NOCONCATENATE before loading 2nd table...!
Hello,
when you load two tables with fields equals qlikview concatenate the two tables, to avoid this, you need add the function NOCONCATENATE
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)
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.
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.