topic Re: Sort Expression by Numbers/Symbols in QlikView

Sort Expression by Numbers/Symbols

Wed, 16 Jul 2014 08:37:39 GMT

Hello,

Is it possible for me to sort my chart in a way where any value with a " < " will always be infront,

values with only numbers will always in the middle

and values with " > " will always be at the back?

For example if I have values of: <1, 3, 6, 7, 8, >9, 2, 4

It will automatically be sorted by: <1 , 2, 3 , 4 , 6, 7, 8 , >9

I understand that I can do something like:

if(Order='<1',1,

if(Order='2',2,

if(Order='3',3,4)))))))

and so on, BUT this method will require me to enter the specific values I want it to be sorted by.

What I want is just to state that:

"<" ,1

number, 2

">" 3

and whenever I choose my dimensions, it will be sorted this way via expressions.

Is it possible and how do I go about doing so?

Thank you!!

Re: Sort Expression by Numbers/Symbols

kaushiknsolanki — Wed, 16 Jul 2014 08:45:54 GMT

Hi,

You can use the "Expression" option of the "Sort" Tab.

Here you can write the expression as

if(wildmatch(Order,'<*'),1,if(Wildmatch(Order,'>*'),100,Order))

Regards,

Kaushik Solanki

Re: Sort Expression by Numbers/Symbols

Anonymous — Wed, 16 Jul 2014 08:47:27 GMT

Hi,

Try something like this:

if (left(Order,1) = '<',

dual(Order,1)

if (left(Order,1) = '>',

dual(Order,9),

num(Order)

)

Then in the chart use sorting by Number to get the right result.

Cheers!

Janusz

Re: Sort Expression by Numbers/Symbols

Siva_Sankar — Wed, 16 Jul 2014 08:50:42 GMT

Hi,

You try using dual function.

dual( s , x )

Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. The dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.

Example:

load dual ( string,numrep ) as DayOfWeek inline

[ string,numrep

Monday,0

Tuesday,1

Wednesday,2

Thursday,3

Friday,4

Saturday,5

Sunday,6 ];

load Date, weekday(Date) as DayOfWeek from afile.csv;

The script example will generate a field DayOfWeek with the weekdays written in clear text. QlikView will for all purposes regard the field as a numeric field.

OR use Custom Sort Order.

Custom Sort Order:

Sometimes a desired sort order does not follow a natural alpha or numeric sort pattern. For example, there may be project phase names that should appear in charts in this specific order:

Design

Review

Budget

Implementation

One of the Sortby options available in chart properties is “Load Order”.

A specific Load Order for a field can be created by loading a dummy table with the desired order prior to loading the source data. The dummy table “PhaseSort” may be dropped after loading the source data.

// Load a dummy table to establish

// sort order for field “Phase”.

PhaseSort:

LOAD * INLINE [

Phase

Design

Review

Budget

Implementation

]

;

// Load the source data

Fact:

LOAD

ProjectId, Phase, Amount

FROM projects.qvd (qvd)

;

// Dummy table no longer needed

DROP TABLE PhaseSort;

Thanks.

Re: Sort Expression by Numbers/Symbols

Wed, 16 Jul 2014 09:21:45 GMT

Hello Kaushik,

Thank you so much for the reply! I've tried this out and I'm able to sort "<" infront and ">" behind. However, the numbers in the middles like 2,3,4,6,7,8 are all jumbled up and not according to ascending order 😞 Sorting by ascending doesn't work either. Any idea how do I solve that?