topic Distinct values within a record. in QlikView

Distinct values within a record.

Wed, 02 May 2012 16:30:58 GMT

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?

Distinct values within a record.

Wed, 02 May 2012 16:41:49 GMT

Do you mean

Table1:

LOAD * INLINE [

Record

EFD

];

Table1:

LOAD * INLINE [

Record

'EFD, EFD, EFD, EFD'

];

Re: Distinct values within a record.

Wed, 02 May 2012 16:44:19 GMT

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?

Re: Distinct values within a record.

Wed, 02 May 2012 16:46:15 GMT

TextBetween(Record, '',',')

Hopefully that works!

Re: Distinct values within a record.

Wed, 02 May 2012 17:56:56 GMT

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?

Re: Distinct values within a record.

Wed, 02 May 2012 18:03:15 GMT

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

Re: Distinct values within a record.

Wed, 02 May 2012 18:07:22 GMT

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;

Re: Distinct values within a record.

jonathandienst — Wed, 02 May 2012 19:40:06 GMT

How about this:

Table1:

LOAD * INLINE [

Record

EFD, EFD, EFD, EFD

];

Table2:

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

Resident Table1;

Regards

Jonathan

Distinct values within a record.

Wed, 02 May 2012 19:52:46 GMT

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;