topic Create table without repeated values in a column in QlikView

Create table without repeated values in a column

Fri, 10 Feb 2012 17:23:34 GMT

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.

Create table without repeated values in a column

swuehl — Fri, 10 Feb 2012 17:43:53 GMT

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?

Create table without repeated values in a column

swuehl — Fri, 10 Feb 2012 17:48:20 GMT

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;

Create table without repeated values in a column

Mon, 13 Feb 2012 14:54:19 GMT

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.

Create table without repeated values in a column

swuehl — Mon, 13 Feb 2012 23:52:53 GMT

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

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

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

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