Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Removal of Duplicates record.

Hi All,

I have a requirement which I have to remove the duplicates from two columns (Bill to customer Name and Customer Part Number)

Before removal of duplicates in Excel

Total sum of Extended Resale : is 287701141.43

After removal of duplicates in Excel

Total Sum of Extended Resale is : 91875042.52

I tried writing the code using Inner keep join but it doesn't help me out much :

[Sheet1]:
LOAD
    Branch,
    "Bill To Customer EB Name",
    "Bill To No",
    "Bill To Customer Name",
    "End Customer Number",
    "End Customer Name",
    "ISR Name",
    "FSR Name",
    "Quote Header Creation Date",
    "Quote Expiration Date",
    "Quote Number",
    "Type",
    "Customer Part Number",
    PM,
    "Assigned PM",
    "Line Number",
    Cost,
    Resale,
    PrAdj,
    "Extended Resale USD",
    GM%,
    BPB,
    Quantity,
    "Customer P/N Desc",
    "Marketing Status",
    "Sales Status",
    "Internal Comment",
    "QLR Comment",
    "DW Status",
    "DW Reg #",
    "DW Type",
    "DW Project",
    "Part Number",
    Manufacturer,
    "Segment 2",
    "Item Status",
    "Item Class",
    "ICC Level 1",
    "ICC Level 2",
    "ICC Level 3",
    DW,
    NCNR,
    BSV,
    CR,
    "SWB Link",
    "FOH Qty",
    "Product Mgr",
    "QLR Date",
    "Target Ext Cost USD",
    "Target Ext Resale USD",
    "Owned By"
FROM [lib://Documents/ODS.SIS.MKT.00019 - Open Quote Details (1).xlsx]
(ooxml, embedded labels, table is Sheet1);
Inner Keep (Sheet1)

[Sheet2]:
LOAD
  "Bill To Customer Name",
    "Customer Part Number" ,
     "Extended Resale USD"
FROM [lib://Documents/ODS.SIS.MKT.00019 - Open Quote Details (1).xlsx]
(ooxml, embedded labels, table is Sheet1);

Attach is the excel file.

Any leads will highly be appreciated.

Thanks and Regards,

Drishti Goel

4 Replies
dwforest
Specialist II
Specialist II

So you are saying Bill To Customer Name (or No?) and Customer Part Number are the unique key for your data, yet you may have multiple rows for them?

In your second table you would need to MOVE the Resale amount and some aggregate function , Max or Min if the value is the same doesn't really matter.

You would then likely want to create a key field to eliminate the synthetic keys and not JOIN the second table but allow it to be separate; you will end up with 2 tables with the second containing the resale amount for each Bill to/Part. Qlik will avoid the fan trap.

Anonymous
Not applicable
Author

Thanks David for your time, but will that be possible for you  to demonstrate it clearly as I'm unable to understand the following statement :

"You would then likely want to create a key field to eliminate the synthetic keys and not JOIN the second table but allow it to be separate; you will end up with 2 tables with the second containing the resale amount for each Bill to/Part. Qlik will avoid the fan trap."

Thanks and Regards,

Drishti Goel

Anonymous
Not applicable
Author

Hi David,

I tried the solution which you gave me but it didn't work as expected. Attach is the qvf file. Can you help me with the solution to achieve this.

Thanks and Regards,

Drishti Goel