Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Remove duplicate row while loading the data into QV

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!

14 Replies
Not applicable
Author

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

maxgro
MVP
MVP

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.

QlikCommunity_Thread_117105_Pic1.JPG.jpg

regards

Marco

Not applicable
Author

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