Defining a Sort Order

[dinicholls]

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!!!

Di

[marcus_malinow]

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];

[ashfaq_haseeb]

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

[#4]

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,

[jonathandienst]

Hi

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

[marcus_malinow]

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