Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Distinct

Good morning everyone !

Please, find below my request :

I would like to remove the duplicates in a column...

My application : TABLE in UPPER, fields lowercase

FOLDER:

idclient,

yearpurchase

It is possible to sort on each of client compared by year of purchase ? DISTINCT but how can I insert it ...

Someone can help me please ...

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: Distinct

can't you just use group by with a resident load? so load your data, then

QUALIFY *;

client_year:

LOAD

only(idclient),

only(yearpurchase)

RESIDENT table1

GROUP BY

idclient,

yearpurchase;

assuming you have called your first table 'table1'. 'Qualify' will ensure that the table doesn't link to your existing table, 'only' is needed as an aggregation function to enable grouping to work

View solution in original post

7 Replies
Highlighted
Master III
Master III

Re: Distinct

Please clarify more...

Highlighted
Champion III
Champion III

Re: Distinct

try

table:

load ,

upper(names) as Names

from table ;

front end

use exp  :    count(Distinct Names)

Highlighted
Creator II
Creator II

Re: Distinct

Hi Chanty, Many thanks for your quick feedback it's working I I don't find the nomenclature of distinct...

If I want to create this same request in my script ?

Highlighted
Master III
Master III

Re: Distinct

You can try like below

table:

load Distinct

upper(names) as Names

from table ;

Highlighted
Creator II
Creator II

Re: Distinct

I try this is not working, Qlikview doesn't accept my expression but is worling ..

CLI_DISTINCT:
noconcatenate load
IDCLIENT,

YEARpurchase

resident DOSSIER_1;

left join (CLI_DISTINCT)

CLI_DISTINCT1:
noconcatenate load
*
resident CLI_DISTINCT;

CLI_DISTINCT2:
noconcatenate load
distinct*

resident CLI_DISTINCT;

DROP Tables CLI_DISTINCT;

Highlighted
Creator II
Creator II

Re: Distinct

we agree that the duplicate is deleted from the second display in the column ...

Sorry, I don't use the right world I thing, the id of client these are numbers...

Highlighted
Creator
Creator

Re: Distinct

can't you just use group by with a resident load? so load your data, then

QUALIFY *;

client_year:

LOAD

only(idclient),

only(yearpurchase)

RESIDENT table1

GROUP BY

idclient,

yearpurchase;

assuming you have called your first table 'table1'. 'Qualify' will ensure that the table doesn't link to your existing table, 'only' is needed as an aggregation function to enable grouping to work

View solution in original post