topic Sorting in Line Charts in QlikView

Sorting in Line Charts

Tue, 04 Jan 2011 08:46:54 GMT

I have a line chart with the following labels at the axis.

S L M 45 60 70 80 90 100 110 216

its currently sorted Numerically & Alphabetically but i want it specifically like this

The L must go to the front, then the numerical numbers in order then the S must be at the back after the 216 or 234

How on earth can i do this?

Sorting in Line Charts

deepakk — Tue, 04 Jan 2011 09:46:58 GMT

hi Ruan,

PFA

I hope this helps.

Sorting in Line Charts

Tue, 04 Jan 2011 10:36:48 GMT

excellent

thanx alot, works like a charm. now for another twist.

S M L 50 60 70 80 90 100 110 120 130 140

The M will have to slot in the middle somewhere like between 90 and 100. is this possible?

Sorting in Line Charts

deepakk — Tue, 04 Jan 2011 10:45:32 GMT

Yes,

if(num(Dim) <100,3, if(Dim ='M', 4,if(num(Dim) >=100,5)))

You can try out the above expr

Sorting in Line Charts

Tue, 04 Jan 2011 10:47:40 GMT

thanx in advance.

Sorting in Line Charts

johnw — Wed, 05 Jan 2011 02:08:59 GMT

Also consider doing a separate load before your main load where you just put the values in the order you want. Then after the main load, drop that table. Sort by "original load order". That lets you easily change the order of distinct values by simply changing their position in the list. See attached.

Sorting in Line Charts

bullish35 — Wed, 05 Jan 2011 02:32:03 GMT

If your dimension values are finite, here's a very simple solution. If you'd rather keep a single table, concatenate the 'SortOder' field. Ellen

Sorting in Line Charts

johnw — Wed, 05 Jan 2011 19:29:17 GMT


Ellen Blackwell wrote:If your dimension values are finite, here's a very simple solution. If you'd rather keep a single table, concatenate the 'SortOder' field. Ellen

It seems like you've taken most of the disadvantages of both Deepak and my solutions and combined them.

Deepak's:
pro - values do not need to be known (if sort rule is simple)
pro - values do not need to be listed (if sort rule is simple)
con - possible extra load time (if adding to the original table and original table is large)
con - complication of data model (extra field or extra table)
con - can be more difficult to add values or change sort order (nested if)

Mine:
pro - trivial to add values or change sort order (insert new values where desired, move other values)
pro - "no" extra load time
pro - no complication of data model
con - values must be known
con - values must all be listed

Yours:
pro - ???
con - possible extra load time (if adding to the original table and original table is large)
con - complication of data model (extra field or extra table)
con - can be more difficult to add values or change sort order (may need to renumber a lot of values)
con - values must be known
con - values must all be listed

Am I missing some advantage of your approach?

Sorting in Line Charts

bullish35 — Sat, 08 Jan 2011 03:49:41 GMT

Pretty talented, am I not? Actually, I doubt that you are missing any hidden advantages. I'm still pretty new. I used this method because it was a really simple way to create the sort order I needed for a very small table. I do appreciate your Pro/Con lists - they have become part of my learning process. Take care. Ellen

Sorting in Line Charts

maneshkhottcpl — Sat, 08 Jan 2011 09:52:30 GMT

Hi,

Simply u can use the wildmatch function in ur chart.

also refer the attached file. u can sort in any order as per ur wish without any script modification.

Sorting in Line Charts

Sat, 08 Jan 2011 15:38:38 GMT

Hi Ruan,

Here is another approach.

Autonumber function will assign unique numeric value for each Dim.

Mapping Load will enable you to define which 'charater sring' value should fit within any possible 'numeric' possible value.

Use the sequence key generated based on the mapping load to sort your Dim in the Line Chart!

Hope this helps. Sample attached.

Please Note: Mapping load is a manual assignnment of numeric values to the Dim, so L gets the lowest value, S the hight value and other text char as per your choice to fit between other numeric Dim values.

sort_map:
Mapping LOAD * Inline [
a, b
S,235
L,0.5
M,101
];

MAP DimSort using sort_map;

Test:
LOAD *, Dim as DimSort;
LOAD * INLINE [
Dim, Value
S, 98
L, 100
M, 67
45, 34
110, 66
60, 44
70, 90
80, 32
100, 11
216, 34
];

TestKey:
NoConcatenate Load Dim, Value, DimSort, autonumber(DimSort) as DimSeqKey resident Test order by DimSort asc;

Drop table Test;

Sorting in Line Charts

johnw — Mon, 10 Jan 2011 19:47:58 GMT


Manesh wrote:Simply u can use the wildmatch function in ur chart.

Or just match() in this case. Good solution for when you can't or don't want to modify script, or when different charts need different sort orders.

Sorting in Line Charts

Tue, 11 Jan 2011 07:08:25 GMT

Thanx Manesh.

This is perfect thanx, does exactly what i need and easy to implement