topic Re: Defining a Sort Order in QlikView

Defining a Sort Order

dinicholls — Wed, 19 Nov 2014 11:45:30 GMT

Hi,

I have a field called 'Dated' which has text in it, that I specify. i.e ME1410, ME1411 191114.

I need to be able to set that specific order of how its sorted. Is there a way to do that?

Thank you!!!

Re: Defining a Sort Order

marcus_malinow — Wed, 19 Nov 2014 11:49:09 GMT

if you have a relatively small set of values, you could load the field as a dual.

For example

SortedTable:

LOAD dual(Text, SortOrder) as Text;

LOAD * INLINE [

Text, SortOrder

ME1410, 1

ME1411, 2

191114, 3];

Re: Defining a Sort Order

ashfaq_haseeb — Wed, 19 Nov 2014 11:52:07 GMT

Hi,

Try something like this in you sort expression.

Dual("Your Month Field",match("Your Month Field" ,'ME1411','ME1410','ME1415'))

The your order the way you like.

Hope it helps.

Regards

ASHFAQ

Re: Defining a Sort Order

Wed, 19 Nov 2014 12:10:02 GMT

First create an inline load apply map:

Lookup_GroupSort:

Mapping
LOAD * INLINE [
Desc, Num
ME1410, 1
ME1411, 2
191114, 3
];

Then in your script where you pull in the field change the straight load to:

dual(Dated, applymap('Lookup_GroupSort', Dated)) AS Dated,

Re: Defining a Sort Order

jonathandienst — Wed, 19 Nov 2014 12:26:56 GMT

I am assuming that the dates with ME denote month end and you have more than the examples in your post. You could construct it something like the below to get them correctly sorted

Set DateFormat = 'DD/MM/YYYY';

// Some way to get date range (may be based on fact table data or

// some dynamic calculation)

Let vStart = Num(Date#('01/01/2013'));

Let vEnd = Num(Date#('31/12/2014'));

// Normal master calendar construction

Calendar:

LOAD SequenceNo,

Date(SequenceNo) As Date,

Date(SequenceNo, 'DDMMYY') As Dated,

If(SequenceNo = Floor(MonthEnd(SequenceNo)), 1, 0) As MonthEnd,

// other calendar fields...

;

LOAD $(vStart) + RecNo() - 1 As SequenceNo

Autogenerate $(vEnd) - $(vStart) - 1;

// Now add the special ME codes

Concatenate(Calendar)

LOAD SequenceNo,

Date(SequenceNo) As Date,

Dual('ME' * Date(SequenceNo, 'YYMM'), SequenceNo) As Dated

Resident Calendar

Where MonthEnd = 1;

// Optionally drop the MonthEnd flag

//DROP FIELD MonthEnd;

You may need to integrate the code into your existing master calendar and adapt the date range selection to your requirements. This assumes the the month end is the calendar month end - if it is not the same, you will need to adapt the calculation in the master calendar load to correctly flag the ME dates.

HTH

Jonathan

Re: Defining a Sort Order

marcus_malinow — Wed, 19 Nov 2014 12:32:52 GMT

Ok, I didn't twig that you had Month End and date based values.

If (Len(keepchar(Dated,'0123456789')=4,

dual(Dated, num(keepchar(Dated,'01234567890') & '00')),

dual(Dated, Dated)) as Dated