topic Re: Add RowNo() to avoid Duplicate key on inputfield in QlikView

Add RowNo() to avoid Duplicate key on inputfield

amber2000 — Fri, 28 Mar 2014 10:26:12 GMT

Hi everyone,

I'm inserting a few inputfields in my QVW.

The moment I do a reload i'm getting the error of Duplicate Key, I've searched this forum and someone came up with the solution

to add a RowNo() result that makes the records unique.

So I'm trying to implement this suggestion but it failes miserabaly.

Can anyone help me to sort this out please?????? If I use the funtion RowNo() an error apears:

Table not found

Palletcontrole:

Add Load RowNo() AS RecordNbr_RowNo,

* Resident RecordNbr

INPUTFIELD To_Give, Given, Remarques;

Palletcontrole:
Add Load RowNo() AS RecordNbr_RowNo,
* Resident RecordNbr;
Drop Table RecordNbr;
Rename table Palletcontrole to RecordNbr;
Exit script;

Load *,

Date(DayStart([Assignment_Date])) As [Date_Picking],
Year([Assignment_Date]) As [Date_Picking Year],
Month([Assignment_Date]) As [Date_Picking Month],
Week([Assignment_Date]) As [Date_Picking Week],
WeekDay([Assignment_Date]) As [Date_Picking Day],
Date(Monthstart([Assignment_Date]),'MM-YYYY') As [Date_Picking MonthYear];

LOAD

    Date#([DT_Start_Dates],'YYYYMMDD') as [Assignment_Date],
    [FK_Employee] As [SD_Employee_Id],
    [FK_Shop] As [SD_Customer_Id],
    [FK_Article] As [SD_Article_Id],
    [FK_Aisle] As [SD_Aisle_Id],
    [BK_Pallet_Cd] As [SD_Pallet],
    [BK_Assignment_Cd] As [SD_Assignment],
    [M_Nr_of_Colli] As [SD_Colli],
    '' AS To_Give,
    '' AS Given,
    '' AS Remarques;

SQL SELECT [DT_Start_Dates],
    [FK_Employee],
    [FK_Shop],
    [FK_Article],
    [FK_Aisle],
    [BK_Pallet_Cd],
    [BK_Assignment_Cd],
    W.[BK_Shop_Cd],
    W.[BK_Shop_Cd],
    A.[BK_Article_Cd],
    A.[Article_Desc],
    A.[Location_Org],
    A.[LogisticalFlow],
    A.[FlowType],
    Emp.[BK_Employee],
    Emp.[Name] + Emp.[FirstName],
    [M_Nr_of_Colli]
FROM DWH.dbo."F_Productivity_Colli"

INNER JOIN DWH.dbo."D_Employee" Emp ON [FK_Employee] = Emp.[SK_Employee]
INNER JOIN DWH.dbo."D_Shop" W ON [FK_Shop] = W.[SK_Shop]
INNER JOIN DWH.dbo."D_Article" A ON [FK_Article] = A.[SK_Article]
INNER JOIN DWH.dbo."D_Aisle" Ail ON [FK_Aisle] = Ail.[SK_Aisle];

Kind regards,

Monique

Re: Add RowNo() to avoid Duplicate key on inputfield

Luis_Cortizo — Fri, 28 Mar 2014 10:35:28 GMT

It's hard to debug the code just by reading it... apparently there's nothing wrong with it.

Just one question out of curiosity, why the resident load?. Why not just add the RowNo() AS RecordNbr_RowNo, on the original table?

Re: Add RowNo() to avoid Duplicate key on inputfield

amber2000 — Fri, 28 Mar 2014 11:34:49 GMT

That is no use because then I get the errormessage back for Duplicate records

Re: Add RowNo() to avoid Duplicate key on inputfield

amber2000 — Fri, 28 Mar 2014 11:38:54 GMT

This is the solution that someone wrote and wich I'm trying to implement in my load script, hopefuly this makes more sense than my code above.

To help identify the problem, add a row number field to each row in the QlikView table.

That will give you at least one field that is unique on each of the duplicate rows.

You can do this without reloading the entire document. Here's an example-- First, save a backup copy of the report that you are working on.

Assume that the table to which you want to add a row number is named REDFLIGHTS. Add these rows to the top of your loadscript:

REDFLIGHTS_X:

Add Load RowNo() as REDFLIGHTS_RowNo, * Resident REDFLIGHTS;

Drop Table REDFLIGHTS;

Rename Table REDFLIGHTS_X to REDFLIGHTS;

Exit Script;

Then, from the menu, select File->Partial Reload This will run the five lines you added to your loadscript and it will build a new table inside the document

that is identical to your original table but with a rownumber field, REDFLIGHTS_RowNo, added to the table.

Now, in a tablebox that includes the rownumber field or in a chart using the key fields as dimensions and using a count(REDFLIGHTS_RowNo)

expression you will be able to identify the duplicate rows in the table. At that point you will have to figure out how to eliminate or

handle the duplicate rows when data is loaded. Remember to remove the five lines from the top of your loadscript before you next try to reload data.