Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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
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,
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
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