topic Re: Sorting by Expression in QlikView

Sorting by Expression

Mon, 26 Jan 2015 06:15:40 GMT

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.

Re: Sorting by Expression

jagan — Mon, 26 Jan 2015 09:58:16 GMT

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.

Re: Sorting by Expression

Mon, 26 Jan 2015 10:12:12 GMT

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?

Re: Sorting by Expression

alexandros17 — Mon, 26 Jan 2015 10:12:49 GMT

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

Re: Sorting by Expression

Tue, 27 Jan 2015 10:18:40 GMT

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

Re: Sorting by Expression

jagan — Tue, 27 Jan 2015 13:20:03 GMT

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.

Re: Sorting by Expression

Tue, 27 Jan 2015 13:52:50 GMT

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?

Re: Sorting by Expression

jagan — Tue, 27 Jan 2015 14:06:18 GMT

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.

Re: Sorting by Expression

Tue, 27 Jan 2015 16:45:16 GMT

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?

Re: Sorting by Expression

rwunderlich — Tue, 27 Jan 2015 19:38:28 GMT

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

Re: Sorting by Expression

Tue, 27 Jan 2015 21:16:19 GMT

Thanks for the soluion, it works perfectly