I have some records that are repeats of values. ie, the Record is EFD, EFD, EFD, EFD.How do I get the record to be just EFD?
Do you mean
LOAD * INLINE [
'EFD, EFD, EFD, EFD'
I mean it's a record that is like the second inline table you have listed, so not a column but a single string. Is there any way to automatically cut the string when the is a comma?
Hopefully that works!
Almost, but what about situations when I have just EFD,EFD for the record or just EFD? Can I use text between so that it picks everything before the first comma?
Picks the string before the first comma or the entire string if there is no comma?
Maybe something like this depending on your situation:
'EFD, EFD, EFD, EFD', 1
'EFD, EFD', 2
IF(( (SubStringCount(Record,','))> 0), TextBetween(Record, '',','),Record) as NewRecord,
' ' as Junk
drop table Table1;
How about this:
EFD, EFD, EFD, EFD
LOAD Distinct SubField(Record, ',') As Result
Jonathan's is a much cleaner solution.
You might also add this to it to unify the results:
LOAD Distinct trim(SubField(Record, ',')) As Result
Retrieving data ...