Avoiding duplicate row for more reloading data from a QVD

[#1]

Hi.

I'm using QV 10 PE. I have created a QVD loader document and a QVW document to consume the data loaded from a QVD.

For more reloading data I can see duplicate rows in list or table boxes.

How can I do avoid this behaviour, please? Thanks

[Miguel_Angel_Baeyens]

Hello,

I don't think I'm getting your issue right, but if you want to avoid loading records from one QVD (say Sales) that don't have correspondence with another table (say Items) you can do something like

Items:LOAD ItemID, ItemNameFROM Items.qvd (qvd); Sales:LOAD DocumentNo, ItemID, Customer, AmountFROM Sales.qvd (qvd) WHERE EXISTS(ItemID); // Only for Items previously loaded

Hope that helps.

[#3]

Hi Miguel,

seeing you examples I have duplicate rows for Items and Sales. Perhaps, I must put a delete inside the loader.

[Miguel_Angel_Baeyens]

If you mean you do have duplicates in your Items.qvd file (or the correspoding in your datamodel) then you may do a distinct load, using maxstring() and grouping by one field that it's not duplicated.

Something like this

Temp:LOAD * INLINE [ItemID, ItemNameA, 1A, 11B, 2B, 22C, 3]; Def:NOCONCATENATE LOAD ItemID, MaxString(ItemName) AS ItemNameRESIDENT TempGROUP BY ItemID; DROP TABLE Temp;

Hope that helps.

[#5]

Hi,

I think I explain better the issue. I read from a relational SQL Server db without any duplicate records.

I have a loader document that stores in QVD files. A file f.e. could contain Items. Another QV document it is used to load from QVD files.

So I have execute the first time the loader and then the QV document that consumes the loaded data. No problems.

Then, I have launched the second time the loader and the 2nd document: now, I have duplicate rows. I think this is an expected behaviour using QVD files and so perhaps there is the right practice to manage this case.

Thanks

[Miguel_Angel_Baeyens]

Hello Andy,

QVD files store no more and no less than you are loading into memory. But if there are concatenations (implicit or explicit) or joins the table in QlikView may add or remove records. Same with partial reloads, when you are using ADD instead of REPLACE or something like that.

But reading in a new empty document the QVD file must return the same number of records the source table had in the moment of the STORE statement.

Another case is that your aggregations or different tables can cause more records to be added. This usually will return unexpected values in expressions.

Anyway, if you can post some simple code that is troubling your document would help.

Regards.

[#7]

Hi.

I have tried to comment pieces of code and I have understood the problem.

In my relational db, I have filled some dimension tables with code written in uppercase mode. In the referenced table, I have written the foreign key in lowercase manner: no violation of relationship has occured, but QV has loaded the dimension tables with code in uppercase and in lowercase and so has duplicated the rows.

For me, QV should manage this case; for SQL Server, there aren't any errors.

Thanks

[Miguel_Angel_Baeyens]

Hello,

Glad to know you have solved the problem.

QlikView is very sensitive in regards to case for both field and values, and this is the first thing to take into account when you are loading data formatted differently. Anyway, to make sure (for example in key fields) that the information is formatted alike, you can use

Upper(KeyField) AS KeyField

In all tables where KeyField will be used, so values will be always the same. Lower() and Capitalize() are good choices as well.

Hope that helps.

[#9]

You can try Load Distinct ... From Items; (it could be, that you have crap in your Item table )

Or Load Distinct From Sales, but this is more dangerous ... you can lose some data you need...