Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Geniuses,
I have various columns in table but I am facing problem with three columns
Below is the small dataset of big problem
SupplierCode | SupplierName | InvoiceNo |
---|---|---|
A02 | AMPTRON | 654789 |
A0003 | A HARTRODT (S) LOGISTICS PTE LTD | 1305115 |
A0003 | A HARTRODT S'PORE PTE LTD | 1305115 |
A0003 | A HARTRODT SINGAPORE PTE LTD | 1305115 |
A03 | ASEC ASIA | PIN010329 |
A03 | ASEC ASIA | PIN010332 |
A03 | ASEC ASIA | PIN010335 |
A03 | ASEC ASIA | PIN010341 |
A03 | ASEC ASIA | PIN010347 |
A03 | ASEC ASIA | PIN010349 |
A03 | ASEC ASIA | PIN010352 |
A03 | ASEC ASIA | PIN010353 |
A03 | ASEC ASIA | PIN010358 |
A03 | ASEC ASIA | PIN010364 |
If you see here for the supplier A003 there are like common names I want only one name out of the 3 but for the supplier A03 as the invoices are differenrt so I wont have problem there
So in nutshell wherever there is the same supplier code and invoice number pick only one supplier name or you can say pick only one row.
Thanks,
Shashank
See this sample here it is
Were is the sample sir?
Hi Shashank, You can remove there duplicates by "Exists" function.
If the Invoice is unique among these three fields then try like below:
Load SupplierCode , SupplierName , InVoiceNo
From sourcetable
where not Exists(InvoiceNo) ;
If the Invoice No is same for different SupplierCode then try like below:
Load * where not exists(Key);
Load SupplierCode , SupplierName , InVoiceNo , SupplierCode & InVoiceNo AS Key
From sourcetable ;
Please find the attached file for reference.
My problem is not the exact names my problem is similar looking names like
A0003 | A HARTRODT (S) LOGISTICS PTE LTD | 1305115 |
A0003 | A HARTRODT S'PORE PTE LTD | 1305115 |
A0003 | A HARTRODT SINGAPORE PTE LTD | 1305115 |
Thanks,
Shashank
Dear Shashank,
This is my suggestion
LOAD SupplierCode,
MinString(SupplierName) as SupplierName,
InvoiceNo
FROM [http://community.qlik.com/thread/107248] (html, codepage is 1252, embedded labels, table is @1)
Group By SupplierCode,InvoiceNo;
Hey Dathu,
I see that it works but why does "Where not exist" exclude the other records in suppliername?
Hi,
Try this script
Data:
LOAD SupplierCode,
MaxString(SupplierName) AS SupplierName,
InvoiceNo
FROM [http://community.qlik.com/thread/107248] (html, codepage is 1252, embedded labels, table is @1)
GROUP BY SupplierCode, InvoiceNo;
Hope it helps you.
Regards,
Jagan.
Hi Michael, the Exists function will works in the same table as well. Its like distinct load on the KEY field. If the any expression is not mentioned the field should be loaded from either previous load. In this case the previous load field assumed from same table.
Hi
i would say load first your table without supplier names
then maintain another one with supplier names and codes like
LOAD code, name from table where not exist(code)
BEWARE code field has to NEVER be loaded before even in an other table under same name
ELSE start by loading this table first
Best regards
Chris