Distinct values in one field

[#1]

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

[Anonymous]

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.

[#3]

I made a little application using subfield ...

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

Thanks

Philippe

[#4]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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;

[#7]

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