Sorting by Expression

[#1]

Hi All

I have a list box showing Weekly dates(from-to), week number in the month and month of the week.

I have written a sort expression to sort by week number "mid(Weeks,12,1) ". I would now like to have a second condition in the sort expression to will sort first by week number, then months in a cronological order. How can I do this?

I would also like my selected dates to show at the top of the list box as they currently at the bottom which I need to scroll down to see.

[jagan]

Hi,

If you have a date field and then use the Dual() for setting the Sorting.

LOAD

*,

DUal(Weeks, WeekEnd(Date)) AS Weeks

FROM DataSource;

Now in sort tab select Numeric.  Hope this helps you.

Regards,

Jagan.

[#3]

Hi Jagan

Thanks for your reply

Please my script for Weeks:

Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')      
&'-'&    
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')   
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY) as Weeks;

How would I apply the Dual fuction here?

[alexandros17]

When I use Week and Month fields I always use the following format:

201501 ... 201552 for weeks

and

201501 ... 201512 for months,

these formats ghive you natural (numeric) sorting

Starting from a date do as follows:

for Weeks: WeekYear(Date)*100 + Week(Date);

for Months: Year(Date)*100 + Month(Date);

Hope this helps

[#5]

Hi Jagan

I think you solution could help me but I'm just strucggling to apply it to my code as it is a bit complex. Can you please assist with that. Please see the script in my previous comment

[jagan]

Hi,

Try this

LOAD

*,

Dual(

Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')      
&'-'&    
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')   
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) -week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY), WeekStart(RE_DAY)) AS Week

Regards,

Jagan.

[#7]

Hi Jagan

Thank you very much for your reply...just one more question.

I'm pulling the data from a SQL table

Can I still perform a Load *, with a preceding load?

[jagan]

Hi,

Use preceding load like below

LOAD

*,

Dual(

Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')      
&'-'&    
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')   
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) -week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY), WeekStart(RE_DAY)) AS Week;

SELECT

*

FROM TableName;

Regards,

Jagan.

[#9]

Hi Jagan

Thanks for the solution. It works perfectly...except now the Dual function is messing up my dates.

I now have a date of 2012/01/01 showing in the list box as "26-31 Week 5 Of Dec". It is suppose to be "01-01 Week 1 Of Jan"

Before adding Dual

After adding Dual

How can I fix this?

[rwunderlich · Accepted solution]

You could also generate a dummy table in the order you want and then use Load Order in the sort.

WeekSort:

LOAD

Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')    

&'-'&  

Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')  

&' Week '&

If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY) as Weeks

;

LOAD MakeDate(2012)+RecNo() as RE_DAY AutoGenerate 2000;

// Do the rest of the script...

DROP TABLE WeekSort;  // Drop dummy table at script end

-Rob