Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Table1:
LOAD * INLINE [
Record
EFD
EFD
EFD
EFD
];
or
Table1:
LOAD * INLINE [
Record
'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?
TextBetween(Record, '',',')
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:
Table1:
LOAD * INLINE [
Record, Other
'EFD, EFD, EFD, EFD', 1
'EFD, EFD', 2
'EFD', 3
];
Table2:
LOAD
IF(( (SubStringCount(Record,','))> 0), TextBetween(Record, '',','),Record) as NewRecord,
Other,
' ' as Junk
Resident Table1;
drop table Table1;
Hi
How about this:
Table1:
LOAD * INLINE [
Record
EFD, EFD, EFD, EFD
];
Table2:
LOAD Distinct SubField(Record, ',') As Result
Resident Table1;
Regards
Jonathan
Jonathan's is a much cleaner solution.
You might also add this to it to unify the results:
Table2:
LOAD Distinct trim(SubField(Record, ',')) As Result
Resident Table1;