Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

siddharthsoam
Contributor

How can i sort row according to a custom name

Hi,

I have a  column category in load editor and I am using it as a row in pivot table as following-

1-3Saved

1-3 Posted

2-5 Saved

2-3 Posted

3-7 Saved

3-7 Posted

I want while appearing in a pivot the saved and posted should appear together.

Expected Output

1-3 Saved

2-5 Saved

3-7 Saved

1-3 Posted

2-3 Posted

3-7 Posted

1 Solution

Accepted Solutions
devarasu07
Honored Contributor II

Re: How can i sort row according to a custom name

Hi,

PFA, try to create calculated dimension  or just keep this expression in your Category field Sort expression as below

=if(Category ='1-3Saved', Dual('1-3Saved',1),

if(Category='2-5 Saved', Dual('2-5 Saved',2),

if(Category='3-7 Saved', Dual('3-7 Saved',3),

if(Category='1-3 Posted', Dual('1-3 Posted',4),

if(Category='2-3 Posted', Dual('2-3 Posted',5),

if(Category='3-7 Posted', Dual('3-7 Posted',6)))))))

Capture.JPG


Capture.JPG


PS: best way try to push back end script (above if will work in back end load script and other chart object


Thanks,

Deva

8 Replies
OmarBenSalem
Esteemed Contributor

Re: How can i sort row according to a custom name

In ur script, create a new field as follow:

load * ,text( replace( col,left(col,find-1),'')) as newField;

load *, FindOneOf("col", 'SP') as find;

load * Inline [

col

1-3Saved

1-3 Posted

2-5 Saved

2-3 Posted

3-7 Saved

3-7 Posted

];

drop field find;

Capture.PNG

and in ur table, sort by expression : len(newField)

Capture.PNG

MVP
MVP

Re: How can i sort row according to a custom name

You can take advantage of the Dual()-function to create a field that has both text and numeric values for each value:

[MYCAT]:

LOAD

   Dual( Txt , Num ) AS MyCat

INLINE [

Txt, Num

1-3 Saved

2-5 Saved

3-7 Saved

1-3 Posted

2-3 Posted

3-7 Posted

];

OmarBenSalem
Esteemed Contributor

Re: How can i sort row according to a custom name

if ur 1st value; I mean 1-3 , 1-5 ... the one that comes before Saved and Posted are always of a length of 3; u can directly sort by expression : Len(YourColumn) without creating the newField

siddharthsoam
Contributor

Re: How can i sort row according to a custom name

Hi Petter,

I already have a category column like this in the database itself, I am not making this in the load editor

siddharthsoam
Contributor

Re: How can i sort row according to a custom name

Hi Omar,

I tried to make a test column close to real data ,in the real data the length between numbers and letters varies.

devarasu07
Honored Contributor II

Re: How can i sort row according to a custom name

Hi,

PFA, try to create calculated dimension  or just keep this expression in your Category field Sort expression as below

=if(Category ='1-3Saved', Dual('1-3Saved',1),

if(Category='2-5 Saved', Dual('2-5 Saved',2),

if(Category='3-7 Saved', Dual('3-7 Saved',3),

if(Category='1-3 Posted', Dual('1-3 Posted',4),

if(Category='2-3 Posted', Dual('2-3 Posted',5),

if(Category='3-7 Posted', Dual('3-7 Posted',6)))))))

Capture.JPG


Capture.JPG


PS: best way try to push back end script (above if will work in back end load script and other chart object


Thanks,

Deva

siddharthsoam
Contributor

Re: How can i sort row according to a custom name

Hi Devarasu,

Is it possible if I can apply colors to the category items in view while using pivot.

devarasu07
Honored Contributor II

Re: How can i sort row according to a custom name

Hi,

u wanted to apply dimension field value or measure value? if measure value u can apply like below,

just add this expression inside your pivot table measure field - background/text Color expression

if(Category='1-3 Posted',green(),

if(Category='2-3 Posted',blue(),

if(Category='2-5 Saved',lightgreen(),

if(Category='3-7 Posted',lightblue(),Yellow()))))


Capture.JPG



Community Browser