Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Create table without repeated values in a column

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

I have something like the next table:

Table 1

N

idclientfilenightssub_idclient
111011006
122011006101
133012004
144013002
155014003
166013002

401

and need to have a new table like this one:

Table2

N
idclientfilenightssub_idclient
122011006201
133012004
155014003
166013002401

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.

Tags (2)
4 Replies
Highlighted
MVP
MVP

Create table without repeated values in a column

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?

Highlighted
MVP
MVP

Create table without repeated values in a column

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;

Highlighted
Not applicable

Create table without repeated values in a column

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.

Highlighted
MVP
MVP

Create table without repeated values in a column

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