Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
The Help provides explanations and examples
https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/synthetic-keys.htm
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