Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Distinct values within a record.

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?

8 Replies
Not applicable
Author

Do you mean

Table1:

LOAD * INLINE [

Record

EFD

EFD

EFD

EFD

];

or

Table1:

LOAD * INLINE [

Record

'EFD, EFD, EFD, EFD'

];

?

Not applicable
Author

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?

Not applicable
Author

TextBetween(Record, '',',')

Hopefully that works!

Not applicable
Author

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?

Not applicable
Author

Picks the string before the first comma or the entire string if there is no comma?

Not applicable
Author

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;

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

How about this:

Table1:

LOAD * INLINE [

   Record

   EFD, EFD, EFD, EFD

];

Table2:

LOAD Distinct SubField(Record, ',') As Result

Resident Table1;

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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;