topic Re: Sort within a field in QlikView

Sort within a field

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Have a field with set of numbers with a delimiter >> ex: (1.4, 5.1, 5.35, 2.4)

Expected result after sort by descending - (5.35, 5.1, 2.4, 1.4)

Is there standard function to apply sort within a field

Thanks,

Umashankar

jonathandienst — Tue, 15 May 2018 05:10:13 GMT

Something like this

LOAD

ID,

'(' & Concat(Token, ',', -Token) & ')' as Result // -Token will sort the tokens in descending order

Group by ID;

LOAD

ID,

SubField(PurgeChar(Field, '()'), ',') as Token //subfield will return one row per token

FROM ....

ID shows how other fields should be handled. The Group By in the upper load should contain all fields except Token.

jonathandienst — Tue, 15 May 2018 05:20:52 GMT

For example:

T_Start:

LOAD * Inline

[

ID| Field

1| (1.4, 5.1, 5.35, 2.4)

2| (0.8, 3.1, 2.35, 6.4)

] delimiter is ('|');

Result:

LOAD

ID,

'(' & Concat(Token, ', ', -Token) & ')' as Result // -Token will sort the tokens in descending order

Group by ID;

LOAD

ID,

Num(SubField(PurgeChar(Field, '()'), ',')) as Token //subfield will return one row per token

Resident T_Start;

umashankarus — Wed, 16 May 2018 12:27:27 GMT

Thanks Jon