Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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;
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.
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