Remove duplicate row while loading the data into QV

[#1]

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!

[#2]

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

[maxgro · Accepted solution]

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]
;

[MarcoWedel]

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

[#5]

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

[MarcoWedel]

you could use a preceding load like:

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

regards

Marco