topic Distinct values in one field in QlikView

Distinct values in one field

Thu, 21 May 2009 15:49:02 GMT

Dear all

I have a rather odd requirement.

In a field i have several StaffId's and mailaddresses seperated by a comma. 55555,66666,77777 etc.

I need this field with distinct values, so the value only appears once for every record. I have used several hours trying with different ideas.

One idea was to split the field by using subfield(UserId,',') then load the distinct values and afterwards combine the values again using previous or peek. But i might be doing something wrong.

Is there any easy solution on this?

Thanks for any help

/Martin

Distinct values in one field

Anonymous — Thu, 21 May 2009 23:38:54 GMT

Martin,
If your method gives you the desired result, nothing is wrong with it. It sounds easy enough. I'd probably done something similar. Well, may not peek(), but left join or function exists(), but it's really is a matter of personal preference.

Distinct values in one field

Thu, 21 May 2009 23:58:30 GMT

I made a little application using subfield ...

Is it something that helps or can you explain a little bit more your needs.

Thanks

Philippe

Distinct values in one field

Fri, 22 May 2009 01:43:41 GMT

That is exactly my problem - i don't get what i want. Everytime i think i've nailed it, i found a value that should be there.

It is easy enough to split the field with subfield and load the distinct values, but i need to merge all the distinct values into one "field" seperated by a comma "," again.

Basically my first field 55555,66666,77777,88888,55555 is okay, but i need to remove the last "55555" as i only want one of each value.

Any ideas?

/Martin

Distinct values in one field

Anonymous — Fri, 22 May 2009 03:04:49 GMT

Hi Martin,

I'm not 100% certain that I understood what you're going for here but something along the lines of this ought to work:

T1:
LOAD * INLINE [
F1, F2
"55555,66666,77777,88888,55555", 1
"88888,44444,33333,22222,44444", 2
];

T2:
Load distinct
subfield(F1,',') as Test,
F2
resident T1;

T3:
load
concat(Test,',') as Test2,
F2
resident T2
group by F2;

drop table T2;

//Jsn

Distinct values in one field

Anonymous — Fri, 22 May 2009 07:53:32 GMT

Oh, I think I understand got what you want:


table_tmp:
LOAD
 RowNo() as ID,
 subfield(Field, ',') as SubField
FROM (Or RESIDENT)...;
//
Table:
LOAD
 ID,
 concat(distinct SubField, ',')
RESIDENT table_tmp
GROUP BY ID;
DROP TABLE table_tmp;

Distinct values in one field

Fri, 22 May 2009 12:52:51 GMT

Michael,

Was that really it? I couldn't find the "reverse subfield function" like Concat, therefore I started out with peek, previous etc.

Thank you very much. I have this report generator and i want to make it a bit safer to use. We split the field "User" within an macro, and selecting those who should receive the specific report (an extract from our AD).

The only issue was if a userid was entered twice in the - then the user will both be selected and deselected.

/Martin