topic Re: Alphabetical order of string in QlikView

Alphabetical order of string

Fri, 23 May 2014 02:00:03 GMT

Hi All,

I have a question regarding transforming a string.

Lets say that my data consist of the ff:

FIELD1	FIELD2
A	YXW
B	CBA
C	ONM

I have to sort FIELD2 alphabetically. What function can I use?

Thanks,

Lyn

Re: Alphabetical order of string

SergeyMak — Fri, 23 May 2014 02:11:59 GMT

You should use Order by.

Data1:

LOAD * INLINE [

FIELD1,	FIELD2
A,	YXW
B,	CBA
C,	ONM

];

Data:

LOAD *

Resident Data1

Order by FIELD2;

Drop table Data1;

Or you need to sort characters in the every value?

Re: Alphabetical order of string

Fri, 23 May 2014 02:32:50 GMT

Hi,

the final result for FIELD2 should be:

A : WXY

B: ABC

C: MNO

Re: Re: Alphabetical order of string

SergeyMak — Fri, 23 May 2014 03:56:38 GMT

PFA

Data1:

LOAD * INLINE [

FIELD1, FIELD2

A, YXW

B, CBA

C, ONMA

];

[tmp]:

LOAD Max(Len(FIELD2)) AS Ln

Resident Data1;

LET vLn=PEEK('Ln');

DROP Table tmp;

NoConcatenate

Data2:

LOAD *,

LEFT(FIELD2,1) AS Field3

Resident Data1;

FOR i=2 to $(vLn)

Concatenate (Data2)

LOAD *,

RIGHT(LEFT(FIELD2,$(i)),1) AS Field3

Resident Data1;

DROP Table Data1;

NoConcatenate

Data3:

LOAD DISTINCT *

Resident Data2;

DROP Table Data2;

NoConcatenate

Data4:

LOAD DISTINCT *

Resident Data3

Order BY FIELD2, Field3;

DROP Table Data3;

Data:

LOAD

FIELD1,

FIELD2,

CONCAT(Field3) AS FIELD4

Resident Data4

GROUP BY FIELD1,FIELD2;

DROP TABLE Data4;

Re: Alphabetical order of string

anbu1984 — Fri, 23 May 2014 05:40:13 GMT

Data1:

LOAD * INLINE [

FIELD1, FIELD2

A,YXW

B,CBA

C,ONM

];

NoConcatenate

Data2:

Load FIELD1, Mid(FIELD2,IterNo(),1) As FIELD3 Resident Data1 While IterNo() <= Len(FIELD2);

NoConcatenate

Data3:

Load FIELD1,Concat(FIELD3) Resident Data2 Group by FIELD1;

Drop table Data1;

Drop table Data2;

Re: Alphabetical order of string

ToniKautto — Fri, 23 May 2014 06:43:48 GMT

The reload performance could be poor for string operations like this, so I would recommend evaluating if the text order could be affected at an earlier stage in the data processing.

Another thing to look at is the reason for re-arranging the string. Could there be an other more efficient way to accomplish the business requirement?

Re: Alphabetical order of string

maxgro — Fri, 23 May 2014 07:31:35 GMT

maybe

SCRIPT

map:

Mapping

load from, '@' & from as to ;

load * inline [

from

];

source:

load * inline [

FIELD1, FIELD2

A, YXW

B, CBA

C, ONM

];

LOAD

FIELD1,

SubField(MapSubString('map', FIELD2), '@') as F

Resident

source;

DROP Table source;

final:

noconcatenate

load

FIELD1,

Concat(F, '', F) as F

Resident t

Group by FIELD1;

DROP Table t;

Re: Alphabetical order of string

tresB — Fri, 23 May 2014 07:34:55 GMT

Massimo Grossi, another interesting idea !