topic Re: Custom Sort in QlikView

Custom Sort

Fri, 14 Nov 2014 15:03:28 GMT

I am pretty newish to Qlikview and I am trying to custom sort a listbox I created.

Currently the data in the listbox looks like this:

0000-0600

0600-1200

0600-1800 (Officer)

0900-1500

1200-1800

1800-0600 (Officer)

1800-2400

What I want is this:

0000-0600

0600-1200

0900-1500

1200-1800

1800-2400

0600-1800 (Officer)

1800-0600 (Officer)

Any ideas?

Thanks

~Norman Rea

Re: Custom Sort

Gysbert_Wassenaar — Fri, 14 Nov 2014 15:05:52 GMT

Sort by expression:

=match(only({1} MyField), '0000-0600','0600-1200','0900-1500','1200-1800','1800-2400','0600-1800 (Officer)','1800-0600 (Officer)')

Or first load the field values in the order you want them in a table in the script before loading the main data. If you do that you can sort by Load Order to get the sorting you want.

Re: Custom Sort

its_anandrjs — Fri, 14 Nov 2014 15:06:53 GMT

Hi,

Try numeric sort option of the chart in the chart properties or load order.

Regards

Anand

Re: Custom Sort

swarup_malli — Fri, 14 Nov 2014 15:13:50 GMT

Norm Rea,

Try coding this in the first sheet of the script editor let it be the first table

[some table name]:

LOAD * INLINE [

NameOfTheListBoxYouWantyToCustomSort
0000-0600

0600-1200

0900-1500

1200-1800

1800-2400

0600-1800 (Officer)

1800-0600 (Officer)

]

;

DROP TABLE [some table name];

should work !

Re: Custom Sort

Fri, 14 Nov 2014 15:14:54 GMT

The values are loaded out of order so no load order and with the numeric sort nothing happens. This date comes form an excel table with columns: Member Name, Shift and Crew. The shift is were these values reside.

~Norm

Re: Custom Sort

its_anandrjs — Fri, 14 Nov 2014 15:16:37 GMT

Hi,

Or another option

LOAD * Inline

[ Recs

0000-0600

0600-1200

0600-1800 (Officer)

0900-1500

1200-1800

1800-0600 (Officer)

1800-2400 ];

Go to properties >> Sort >> write expression Len(Recs)

Note:- Assume this is the example and in place of Recs use your fieldname.

Regards

Anand

Re: Custom Sort

Fri, 14 Nov 2014 15:16:52 GMT

Problem data can change so it is loaded from and excel sheet.

Re: Custom Sort

Fri, 14 Nov 2014 15:19:20 GMT

Close but my 0900-1500 is not in the right place.

Re: Custom Sort

its_anandrjs — Fri, 14 Nov 2014 15:31:20 GMT

Hi,

If this is identified and confirmed the length of records which is numeric is 9 then try the below script

LOAD * Inline

[ Recs

0000-0600

0600-1200

0600-1800 (Officer)

0900-1500

1200-1800

1800-0600 (Officer)

1800-2400 ];

New:

LOAD Recs, RowNo() as Rid Resident A Where Len(Recs) = 9;

Concatenate

LOAD Recs, RowNo() as Rid Resident A Where Len(Recs) > 9;

DROP Table A;

And sort it by Rowid

Regards

Anand

Re: Custom Sort

morganaaron — Fri, 14 Nov 2014 15:40:09 GMT

Did you also leave Numeric Value (Ascending) ticked? I've got both selected and it seems to work okay..

Re: Custom Sort

Fri, 14 Nov 2014 15:44:57 GMT

I did check it and it has no effect. I feel that my listbox is just text.

~Norm

Re: Custom Sort

Peter_Cammaert — Fri, 14 Nov 2014 15:46:39 GMT

This looks stupid, but it works:

Set the Sort Order of your listbox to Expression, and enter this expression into the corresponding field:

=num(left(Recs, 4)) + div(len(Recs),10)*10000

If your field uses a different name, then replace all occurrences of Recs with that name. See attachment.

Best,

Peter

Re: Custom Sort

jagan — Fri, 14 Nov 2014 16:20:21 GMT

Hi,

Try like this

LOAD

FROM

ColumnName

0000-0600

0600-1200

0900-1500

1200-1800

1800-2400

0600-1800 (Officer)

1800-0600 (Officer) ];

YourTableName:

LOAD

ColumnName,

FROM DataSource;

Now in Sort tab select Load Order.

Re: Custom Sort

Fri, 14 Nov 2014 16:31:27 GMT

Getting close....The data I load comes from Excel. It pulls the different shifts into Qlikview in a variety of orders depending on how people in my fire station sign up for duty.

~Norm

Re: Custom Sort

Peter_Cammaert — Fri, 14 Nov 2014 16:39:08 GMT

Did you try one more last time with a Sort Expression?

Re: Custom Sort

Fri, 14 Nov 2014 17:11:41 GMT

This is an example of the Excel Table I am pulling into Qlikview.

Date	Member	Crew	Shift	Coverage
1/1/2014	Britt Williams	Duty Schedule	0000-0600	Complete Shift
1/1/2014	Amanda Dean	Duty Schedule	0000-0600	Complete Shift
1/1/2014	Britt Williams	Duty Schedule	0600-1200	Complete Shift
1/1/2014	Amanda Dean	Duty Schedule	0600-1200	Complete Shift
1/1/2014	David Hanson	Duty Schedule	1200-1800	Complete Shift
1/1/2014	Britt Williams	Duty Schedule	1200-1800	Complete Shift
1/1/2014	James Morgan	Duty Schedule	1800-2400	Complete Shift
1/1/2014	Preston Pratt	Duty Schedule	1800-2400	Complete Shift
1/2/2014	Charles Dowell	Duty Officer	1800-0600	Complete Shift
1/2/2014	James Morgan	Duty Schedule	0000-0600	Complete Shift
1/2/2014	Preston Pratt	Duty Schedule	0900-1500	Complete Shift
1/2/2014	Kiersten Shea	Duty Schedule	1200-1800	Complete Shift
1/2/2014	Michael Heim	Duty Officer	0600-1800	Complete Shift
1/3/2014	Rick Tallman	Duty Schedule	0000-0600	Complete Shift

Re: Custom Sort

Fri, 14 Nov 2014 17:28:46 GMT

I got it working with a combo of the expression Len and Text Sort. Only new issue I found is that once you select something the sorting function is thrown out the window. Any clues on that?

~Norm

Re: Custom Sort

jduenyas — Fri, 14 Nov 2014 18:53:51 GMT

Norm

From the attached spreadsheet you provided the column Shift does not have the word '(Officer)' attached to it.

It seems that you are concatenating that at load time(?) The sort is Alpha sort and not numeric therefor the 2 rows that include (officer) are not at the bottom as you want it in the sample you provided.

You will have to create a separate key (or field) on which to sort, preferably numeric. The sort should not be changed or affected at time of selecting a line and then releasing the selection.

Hope this helps.

Josh

Re: Custom Sort

Anonymous — Fri, 14 Nov 2014 19:15:22 GMT

Hi.

You can prepare an excel with two column as the following table:

Shift Shift_sort

0000-0600 1

0600-1200 2

0900-1500 3

1200-1800 4

1800-2400 5

0600-1800 (Officer) 6

1800-0600 (Officer) 7

Then, in editor script, after loading your data, you should make a left join with this table so you will have a new field: you can use Shift_sort as expression in tab Sort of your listbox (flag option "Expression" and insert just the field name Shift_sort).

Elena

Re: Custom Sort

MarcoWedel — Fri, 14 Nov 2014 23:35:31 GMT

Hi,

given your sample data, I guess one solution might be:

tabShifts:
LOAD *,
    Timestamp(Date+ShiftStartTime, 'MM/DD/YYYY hh:mm') as ShiftStartDateTime,
    Timestamp(Date+ShiftStartTime+ShiftDuration, 'MM/DD/YYYY hh:mm') as ShiftEndDateTime;
LOAD *,
    Interval(ShiftEndTime-(ShiftEndTime<ShiftStartTime)-ShiftStartTime,'hh:mm') as ShiftDuration;
LOAD Date,
    Member,
    Crew,
    Shift,
    Time(Time#(SubField(Shift,'-',1),'hhmm'),'hh:mm') as ShiftStartTime,
    Time(Time#(SubField(Shift,'-',2),'hhmm'),'hh:mm') as ShiftEndTime,
    Coverage
FROM [http://community.qlik.com/servlet/JiveServlet/download/657236-137574/Duty%20Data%20Example.xlsx]
(ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco