Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharthsoam
Partner - Creator II
Partner - Creator II

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
Master II
Master II

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

View solution in original post

8 Replies
OmarBenSalem

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

petter
Partner - Champion III
Partner - Champion III

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

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
Partner - Creator II
Partner - Creator II
Author

Hi Petter,

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

siddharthsoam
Partner - Creator II
Partner - Creator II
Author

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
Master II
Master II

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
Partner - Creator II
Partner - Creator II
Author

Hi Devarasu,

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

devarasu07
Master II
Master II

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