topic Duplicates in QlikView

Duplicates

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have a field called Seller.

We pull the seller detail from two systems like SQL DB and teradata.

After we load the information in qlikview we got duplicates due to case sensitive as below.

Seller sales

4thmm 10

4Thmm 10

98ku 20

98KU 20

tte 30

TTe 30

when we do a sum(sales) as dimension as seller we got huge count due to duplicates.

How to remove the duplicated.

I have used the upper(Seller) ,but it removes the seller name start with numeric.

Thanks..

Re: Duplicates

kaushiknsolanki — Thu, 25 Jan 2018 09:49:45 GMT

Try this.

LOad Distinct Upper(Seller) as Seller,sales inline [

Seller, sales

4thmm, 10

4Thmm, 10

98ku, 20

98KU, 20

tte, 30

TTe, 30

];

Regards,

Kaushik Solanki

Re: Duplicates

zhadrakas — Thu, 25 Jan 2018 09:50:07 GMT

i tried this and it seems to work correct

Load Distinct lower(Seller) as Seller,
sales
;

LOAD * INLINE [
Seller ,sales
4thmm ,10
4Thmm ,10
98ku ,20
98KU ,20
tte ,30
TTe ,30
];

Re: Duplicates

nareshthavidishetty — Thu, 25 Jan 2018 10:23:25 GMT

Hi,

We don't need to change the case of the seller.

Instead of that is it possible to pull only first value.

Thanks..

Re: Duplicates

zhadrakas — Thu, 25 Jan 2018 10:27:27 GMT

upper or lower doesnt matter at this Point.

The important word for you is Load distinct

Re: Duplicates

sasikanth — Thu, 25 Jan 2018 10:57:27 GMT

Hi,

While fetching data from database use UPPER function in every load and then store data into QVDS.

DB Load:

Load UPPER(Seller ) as Seller,

sales ;

SQL Select * from scehma.dbo.table;

Load QVDs in to qlikview

Load Seller , sales from SQLQVD.qvd;

Load Seller,sales from TERADATA.qvd where not exists(Seller) ;

Re: Duplicates

nareshthavidishetty — Thu, 25 Jan 2018 10:57:52 GMT

Hi,

Its not working.

Thanks...

Re: Duplicates

sasikanth — Thu, 25 Jan 2018 11:02:13 GMT

Please share your application if you can

Re: Duplicates

nareshthavidishetty — Thu, 25 Jan 2018 11:21:36 GMT

Hi,

Attached is the sample data.

Thanks..

Re: Duplicates

nareshthavidishetty — Thu, 25 Jan 2018 12:32:26 GMT

Hi,

Any suggestions.

Thanks..

Re: Duplicates

Or — Thu, 25 Jan 2018 12:38:21 GMT

I can confirm that the Lower() approach suggested above works correctly, as does Upper() and Capitalize(). These have to be done in LOAD, not in your SELECT - otherwise your DBs may treat strings differently.

You do need to change the case of the seller, because AAA and aaa are NOT the same thing. You have to pick one and remove the other, and the way to do that is to use lower(), upper(), or capitalize().