topic Re: same id with different names - get longer name in QlikView

same id with different names - get longer name

rafael5958 — Fri, 23 Aug 2019 00:06:44 GMT

My situation, I have:

id     name
1      jack S
1      jack Soon
1      jack Son
2      Ray J
2      Ray James Charles
2      Ray J C
3      Mark

I need to select only one ID with the longer name, or at lesat only one name. Result something like:

id     name
1      jack Soon
2      Ray James Charles
3      Mark

in SQL I would do something like:

select id,       
max(name) keep (dense_rank first order by length(name) desc)
from t
group by id;

But the table is already loaded in qliview from the database, how would I create a new table with that statement?

Re: same id with different names - get longer name

jmartineze — Fri, 23 Aug 2019 08:01:23 GMT

Hello Rafael,

Try something like this

Load id, name, length(name) as N

from "Table";

noconcatenate

load id, max(N)as N resident A group by id;

inner join load * resident A;

drop table A;

Re: same id with different names - get longer name

rafael5958 — Fri, 23 Aug 2019 12:36:48 GMT

Didnt work.

It retrives

id	N
1	9
1
2	17
2
3	4

There was not length funcion, only len.

sample: LOAD * INLINE [ id, name 1, jack S, 1, jack Soon, 1, jack Son, 2, Ray J, 2, Ray James Charles, 2, Ray J C, 3, Mark ]; A: Load id, name, Len(name) as N resident "sample"; noconcatenate B: load id, max(N)as N resident A group by id; inner join load * resident A; drop table A;

Re: same id with different names - get longer name

jmartineze — Fri, 23 Aug 2019 12:42:22 GMT

Hello, change the last line by

drop tables A, sample;

Re: same id with different names - get longer name

sunny_talwar — Fri, 23 Aug 2019 12:47:37 GMT

You can also try this

sample: LOAD *, Len(name) as Name_Len; LOAD * INLINE [ id, name 1, jack S 1, jack Soon 1, jack Son 2, Ray J 2, Ray James Charles 2, Ray J C 3, Mark ]; Right Join (sample) LOAD id, Max(Name_Len) as Name_Len Resident sample Group By id; DROP Field Name_Len;

Re: same id with different names - get longer name

rafael5958 — Fri, 23 Aug 2019 12:51:12 GMT

thank you all, it worked.