Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have this table
Names |
---|
George, George, John, Chris |
Helen, John |
John, John, John,John |
and i want to create a new column with this output
Output |
---|
George, John, Chris |
Helen, John |
John |
any ideas?
You can do it like this (first splitting the field value into parts, then re-combining using a distinct concat):
Names:
LOAD Concat(DISTINCT Name,',') as Names
GROUP BY RecID;
LOAD recno() as RecID, Trim(SubField(Names,',')) as Name;
LOAD * INLINE [
Names
"George, George, John, Chris"
"Helen, John"
"John, John, John,John"
];
You can do it like this (first splitting the field value into parts, then re-combining using a distinct concat):
Names:
LOAD Concat(DISTINCT Name,',') as Names
GROUP BY RecID;
LOAD recno() as RecID, Trim(SubField(Names,',')) as Name;
LOAD * INLINE [
Names
"George, George, John, Chris"
"Helen, John"
"John, John, John,John"
];
Hi,
Try this,
Data:
Load Names, Trim(SubField(Names, ',')) as TempField;
LOAD * INLINE [
Names
"George, George, John, Chris"
"Helen, John"
"John, John, John,John"
];
Final:
LOAD Names,
Concat(Distinct TempField, ',') as NamesNew
Resident Data
Group by Names;
Drop Table Data;