Create table without repeated values in a column

[#1]

Please if anyone can help me with this (I suspect its simple):

I have something like the next table:

Table 1

N	idclient	file	nights	sub_idclient
11	101	100	6
12	201	100	6	101
13	301	200	4
14	401	300	2
15	501	400	3
16	601	300	2	401

and need to have a new table like this one:

Table2

N	idclient	file	nights	sub_idclient
12	201	100	6	201
13	301	200	4
15	501	400	3
16	601	300	2	401

where duplicated values from file are eliminated and only remain the ones that have any value on sub_idclient (and the unique values in file).

Thank you.

[swuehl]

So, if you have multiple entries for file, can we assume that you always have one and only one value set in sub_idclient? And will the record with value in sub_idclient have the largest N of that subset?

[swuehl]

If so, you could try something like

INPUT:

LOAD N,

     idclient,

     file,

     nights,

     sub_idclient

FROM

[http://community.qlik.com/thread/47073?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD

max(N),

firstsortedvalue(idclient, -N),

file,

firstsortedvalue(nights, -N),

firstsortedvalue(sub_idclient, -N)

resident INPUT group by file;

[#4]

Yes, there will be only one value set in sub_idclient. Not necessarily the largest value of N will be the remaining record in sub_idclient, it will be the record distinct than 0 or blank in sub_idclient when file values are duplicated.

[swuehl]

Ok, I think then you can try this:

INPUT:

LOAD N,

     idclient,

     file as inputfile,

     nights,

     sub_idclient

FROM

[http://community.qlik.com/thread/47073?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

NOCONCATENATE  LOAD N,idclient,inputfile as file,nights,sub_idclient resident INPUT where len(sub_idclient);

LOAD N,idclient,inputfile as file,nights,sub_idclient resident INPUT where not exists (file,inputfile);

drop table INPUT;

So first load only the lines with the sub_idclient set (I used len(sub_idclient) to check, but you may double check if this is appropriate), then I do a second load that loads all other records (with a different file value).

Hope this helps,

Stefan