topic Re: Remove duplicate row while loading the data into QV in QlikView

Remove duplicate row while loading the data into QV

Sun, 04 May 2014 05:15:39 GMT

Hi,

I have the sample data set. How could I write the script while I have the duplicate in the original data set and I would like to remove the duplicate while loading the data into QV?

Thanks so much!

Re: Remove duplicate row while loading the data into QV

Sun, 04 May 2014 05:33:07 GMT

Have you tried the DISTINCT statement?

Load distinct

A, B, B

from .xls

regards,

Marcelo

Re: Remove duplicate row while loading the data into QV

Sun, 04 May 2014 05:35:02 GMT

Yes I have tried this once.

But It seems that when the dataset is big and there is lots of filed, it could not work well...Is there any other method?

Thanks so much!

Re: Remove duplicate row while loading the data into QV

Sun, 04 May 2014 05:45:18 GMT

It sounds very strange that Distinct function is not working correctly.

You also could tried the group by, with the entire set or fields.

regards,

Marcelo

Re: Remove duplicate row while loading the data into QV

Sun, 04 May 2014 05:47:47 GMT

For the distinct function, is there any restriction for the null value?

Re: Remove duplicate row while loading the data into QV

Sun, 04 May 2014 06:55:10 GMT

If I would like to remove the duplicate by only one filed, how could I use the distinct function?

Re: Remove duplicate row while loading the data into QV

PrashantSangle — Sun, 04 May 2014 07:18:15 GMT

Hi,

In provided data which field is duplicate?

Regards,

Re: Remove duplicate row while loading the data into QV

Sun, 04 May 2014 07:21:47 GMT

The first two fileds: 'YearMonth' and 'percent of Pass'

Re: Re: Remove duplicate row while loading the data into QV

PrashantSangle — Sun, 04 May 2014 07:41:45 GMT

Hi,

What issue you are getting while working with

Distinct

Group By Clause.

See Enclosed File.

and check frequency of each value.

Regards,

Re: Remove duplicate row while loading the data into QV

maxgro — Sun, 04 May 2014 07:48:20 GMT

The first 2 rows have the same value for Yearmonth and Percent ofPass

What's your required output? 1 rows? If yes how do you choose which value to keep for other fields?

Re: Remove duplicate row while loading the data into QV

Sun, 04 May 2014 07:53:04 GMT

yes 1 row. I would like to choose the min(Yearmonth2) as the restriction.

Re: Re: Remove duplicate row while loading the data into QV

maxgro — Sun, 04 May 2014 08:46:33 GMT

if you want to choose the row with minimum Yearmonth2

source:
LOAD 
  Yearmonth & '-' & [Percent of pass] as Field,
  Yearmonth, 
     [Percent of pass], 
     Grade, 
     Teach, 
     Yearmonth2, 
     [Percent of pass2], 
     Grade2, 
     Teach2, 
     Yearmonth3, 
     [Percent of pass3], 
     Grade3, 
     Teach3, 
     Yearmonth4, 
     [Percent of pass4], 
     Grade4, 
     Teach4
FROM
SampleData.xlsx
(ooxml, embedded labels, table is Sheet1);


table:
NoConcatenate load
  *
Resident source
where peek(Field) <> Field
order by Yearmonth, [Percent of pass], Yearmonth2
;

DROP Table source;

another one: if you want to choose using different logic for different fields you can use group by

LOAD Yearmonth, 
     [Percent of pass], 
     MinString(Grade) as Grade, 
     MinString(Teach) as Teach, 
     min(Yearmonth2) as Yearmonth2, 
     min([Percent of pass2]) as [Percent of pass2], 
     MinString(Grade2) as Grade2, 
     MinString(Teach2) as Teach2, 
     max(Yearmonth3) as Yearmonth3, 
     min([Percent of pass3]) as [Percent of pass3], 
     MinString(Grade3) as Grade3, 
     MinString(Teach3) as Teach3, 
     min(Yearmonth4) as Yearmonth4, 
     min([Percent of pass4]) as [Percent of pass4], 
     MinString(Grade4) as Grade4, 
     MinString(Teach4) as Teach4
FROM
SampleData.xlsx
(ooxml, embedded labels, table is Sheet1)
group by 
 Yearmonth, 
     [Percent of pass]
;

Re: Remove duplicate row while loading the data into QV

MarcoWedel — Sun, 04 May 2014 23:28:25 GMT

Hi Jelly,

you could try

LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/521529-104823/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where not (Exists(Yearmonth) and Exists([Percent of pass]));

also.

regards

Marco

Re: Remove duplicate row while loading the data into QV

Fri, 16 May 2014 00:15:48 GMT

Hi Macro

Thanks so much for your help. I have a further question. Your code is suit for the situation that we load data from excel. But If I want to load data using SQL from database, how could I use your method?

Jelly

Re: Re: Remove duplicate row while loading the data into QV

MarcoWedel — Fri, 16 May 2014 08:30:30 GMT

you could use a preceding load like:

LOAD *
Where not (Exists(Yearmonth) and Exists([Percent of pass]));
SQL SELECT *
FROM YourTable;

regards

Marco