topic Order by in QlikView

Order by

Fri, 27 May 2011 09:38:49 GMT

Hello everyone,

I need a litle help here, hope that its possible:

I'm trying to map months with then respectives codes, somethink like that:

Map1:

Mapping Load*Inline [cod_month, month

1, Jan

....

12, Dec]

Then i'm doing the apply map into other load:

Load a, b, c, apply map('Map1', c_month ) as month, e, f, g from .....;

Now i would like to order the months from january to december.

I tried the order by function with the resident of Map1 but its not really working.

Any suggestions?

Best regards,

Eliano Marques

Order by

kaushiknsolanki — Fri, 27 May 2011 10:46:34 GMT

Hi,

You can do one thing while loding data you can give an expression as

if(Month = 'Jan',1,

if(Month = 'Feb',2,............ )))) as month_Order

Hope this will help you

Regards,

Kaushik Solanki

Order by

Anonymous — Fri, 27 May 2011 11:02:08 GMT

Hi, I assume your c_months are 1, 2, 3, 4 since your mapping is ordered that way? If yes, why not have both the c_month value (1, 2...) and the mapped month name in the record. You can then easily sort it that way, e.g.:

Load a, b, c, c_month, ApplyMap('Map1', c_month) as month

from [somewhere]

Order by

Fri, 27 May 2011 11:23:02 GMT

I did that.

I was just wondering if somehow with the order by function we could do that.

Anyway thanks.

Re: Order by

Anonymous — Fri, 27 May 2011 11:29:59 GMT

Hmmm, not sure I'm following completely. Your original values (1,2,3..) are allready sortable, so don't understand the issue. You could order by c_month (1, 2, 3...), before you do the mapping.

EDIT: Or are you asking if you can sort on Jan, Feb...Dec? Since this is just a string without an underlying number, numeric sorting will not give you Jan, Feb, but rather Apr, Aug and so on. You could map a date to your 1,2,3 values instead...

Order by

Fri, 27 May 2011 16:44:19 GMT

Every value in QlikView is a dual: a string associated to a double.

So you can assign both a Text and Numeric value to the month column:

Load a, b, c, Dual(ApplyMap('Map1', c_month), c_month) as month, e, f, g from .....;

So you can sort month by numeric value.

Bye!

Order by

Anonymous — Fri, 27 May 2011 16:48:44 GMT

So nice, thanx.