Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Distinct values within a record.

Do you mean

Table1:

LOAD * INLINE [

Record

EFD

EFD

EFD

EFD

];

or

Table1:

LOAD * INLINE [

Record

'EFD, EFD, EFD, EFD'

];

?

Not applicable

Re: Distinct values within a record.

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

Re: Distinct values within a record.

TextBetween(Record, '',',')

Hopefully that works!

Not applicable

Re: Distinct values within a record.

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

Re: Distinct values within a record.

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

Not applicable

Re: Distinct values within a record.

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;

MVP
MVP

Re: Distinct values within a record.

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

Distinct values within a record.

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;

Community Browser