topic Re: Sorting Issues in QlikView

Sorting Issues

akpofureenughwu — Sat, 25 Feb 2017 06:23:11 GMT

Good morning everyone,

I have been having issue sorting lately...

I used the script:

Match ([Repair.Month], 'January','February','March','April','May','June','July','August','September','October','November','December')

But stilll the data is not sorted by month,,,,

Please kindly help me review it.

Regards

See attached

Re: Sorting Issues

ecolomer — Sat, 25 Feb 2017 06:54:42 GMT

Are you sure that the "Repair Month" field has a date format?

It is possible the reason for the behaviour you experienced is that you already loaded your month_fullnames as a date field formatted as month fullnames. So the proposed solutions using the Date# function didn't find a string to interpret as a date (that's what the #-functions do).

On the other hand this means, that your front end expression

=Month(Date(Month_FullNames,'MMM'))

can be abbreviated to

=Month(Month_FullNames)

as the formatting date() function is useless in this case, it does not change the underlying numerical value of your Month_FullNames field (Repair Month).Using this expression

Date(Date#(SubField('$(MonthNames)',';',MONTH),'MMM'),'MMMM') as [Repair Month]

to create your [Repair Month] field in the script leads to somewhat weird underlying numerical values:

because you are trying to create a complete date value from only a month field (the year information is missing) and then just formatting it to show month fullnames.I therefore stick to my proposed solution

Dual(Date(Date#(MONTH,'MM'),'MMMM'), MONTH) as [Repair Month]

which creates these underlying values:

like one would expect from a real month field. If you wanted to create the month shortname field in the script, then one solution could be:

Month(Date#(MONTH,'MM')) as [Repair Month]_short

hope this helps

Re: Sorting Issues

priyasawant — Sat, 25 Feb 2017 06:56:24 GMT

Hi.. kindly Keep repair month on 1st and then Number Of service Order in sorting tab

Re: Sorting Issues

HirisH_V7 — Sat, 25 Feb 2017 06:56:28 GMT

Hi,

Use this expression in the sort and Keep Ascending,

=Num(Month((Date#(Month,'MMM'))))

HTH,

Hirish

Re: Sorting Issues

akpofureenughwu — Sat, 25 Feb 2017 10:52:29 GMT

Hello sir,

Thank you for your response. Still have issue sort out the month....

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET CollationLocale='en-GB';

SET CreateSearchIndexOnReload=1;

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Here is the script

LIB CONNECT TO [sql];

Qualify*;

['store']:

LOAD

[Item Supplied],

[Year ] AS [Year],

[Month],

[Amount];

SQL SELECT "Item Supplied",

"Year ",

"Month",

"Amount"

FROM "Admin"."dbo"."'store'";

[Repair]:

LIB CONNECT TO 'sql';

LOAD Date(Date#(SubField('$(MonthNames)',';',"Month"),'MMM'),'MMMM') as [Repair Month],

"Year",

"No of Service Order",

" Amount";

SQL SELECT "Month",

"Year",

"No of Service Order",

" Amount"

FROM Admin.dbo."'Repair of Office Equipment $'";

Re: Sorting Issues

akpofureenughwu — Sat, 25 Feb 2017 11:00:59 GMT

That's the format I arranged it. I don't think it will make a difference

Re: Sorting Issues

akpofureenughwu — Sat, 25 Feb 2017 11:02:14 GMT

I just checked the data type of month. (repair_month) is not a date format..

Re: Sorting Issues

rahulpawarb — Sat, 25 Feb 2017 11:29:51 GMT

Please use given expression under the sort tab - sort by expression (deselect all other options):

=Date([Repair Month],'MM')

Regards!

Rahul

Re: Sorting Issues

akpofureenughwu — Sat, 25 Feb 2017 12:18:37 GMT

Hello Rahul

Thank you for your response.

The =Date([Repair Month],'MM') expression was helpful, it helped me to sort 0ut the month data. I tried using the saME expression for the month field on the table but it didn't work.

Please find the attached file for clarity

Thank you !

Re: Sorting Issues

rahulpawarb — Sat, 25 Feb 2017 12:26:55 GMT

Hello Akpofure,

Please share the application with sample data. This will help to further analyze this issue.

Regards!
Rahul

Re: Sorting Issues

akpofureenughwu — Sat, 25 Feb 2017 14:09:00 GMT

Hello..... Rahul are you there?

It's a sql query generated data.

Re: Sorting Issues

rahulpawarb — Sat, 25 Feb 2017 14:15:11 GMT

Hello Akpofure,

Sorry I was away for while. BTW, you can share the application file.

Regards!

Rahul

Re: Sorting Issues

akpofureenughwu — Sat, 25 Feb 2017 14:39:21 GMT

Here is a sample

Re: Sorting Issues

MarcoWedel — Sun, 26 Feb 2017 01:38:27 GMT

Hi,

one solution might be

Dual(Month, Month(Date#(Month,'MMMM'))) as Month

hope this helps

regards

Marco

Re: Sorting Issues

rahulpawarb — Sun, 26 Feb 2017 08:03:28 GMT

Hello Akpofure,

I agree with Marco. You try the suggested approach. BTW, you can change the sort order under Sorting tab of Table object (New Sort Order - Unit, Year, Month).

Hope this will be helpful.

Regards!

Rahul

Re: Sorting Issues

MarcoWedel — Mon, 27 Feb 2017 00:03:30 GMT

Please close your thread if your question is answered.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks

Regards

Marco